Hi, I am pulling text files in gzip format from UNIX system. I want to unzip these files and then import data from these files into database using SSIS.

Run your favorite unzip utility (there are plenty of gzip-compatible archivers out there, including gzip itself) using Execute Process task.

In our environment, we are not allowed to load 3rd party software to unzip a file. However, we did create a VB Script file that accepts an argument for filename and a second argument for destination directory. We use CopyHere to accomplish the task letting WScript handle execution. This involves keeping a vbs file on the server. However, we would ideally like to use the CopyHere (or some similar command) from within SSIS. Any suggestions? Thanks

Using a script task, you should be able to execute any VB.Net command you wish.

After figuring out the correct search criteria to type into Google, I found the website: http://www.vbprofs.com/Source_Codes/.NET/Zip%10Unzip_files_from_your_VB.NET_application/ In it, Thomas Kaloyani describes Zipping and Unzipping using ONLY resources accessible to SSIS. He includes code for zipping and unzipping. I had previously done this using VBScript in DOS using WScript, but this version seems to be many times faster at unzipping. The one thing not shown in the code is to make reference to the Visual J# library so you can use the Imports java.util.zip so ADD REFERENCE: vjslib.dll -- part of the MS .NET framework. I want to make sure credit is given to the correct author: Thomas Kaloyani. In it, he gives credit to Valeri on the code project website: http://www.codeproject.com/KB/cs/vmeasyzipunzip.aspxfor pointing him in the right direction. All I did wasduplicate what we needed from Thomas' version to work for our case. The source file and destination directoryshould be changed to bevariable.Here is the code for Unzipping... Code Block ' Microsoft SQL Server Integration Services Script Task ' Write scripts using Microsoft Visual Basic ' The ScriptMain class is the entry point of the Script Task. Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Runtime Imports java.util.zip Public Class ScriptMain ' The execution engine calls this method when the task executes. ' To access the object model, use the Dts object. Connections, variables, events, ' and logging features are available as static members of the Dts class. Public Sub Main() Try Dim strSourceFile As String Dim strDestinationDirectory As String strSourceFile = "C:\testzip\filename.zip" strDestinationDirectory = "C:\testzip\Unzipped\" Dim oFileInputStream As New java.io.FileInputStream(strSourceFile) Dim oZipInputStream As New java.util.zip.ZipInputStream(oFileInputStream) Dim bTrue As Boolean = True Dim sbBuf(1024) As SByte While 1 = 1 Dim oZipEntry As ZipEntry = oZipInputStream.getNextEntry() If oZipEntry Is Nothing Then Exit While If oZipEntry.isDirectory Then If Not My.Computer.FileSystem.DirectoryExists(strDestinationDirectory & oZipEntry.getName) Then My.Computer.FileSystem.CreateDirectory(strDestinationDirectory & oZipEntry.getName) End If Else Dim oFileOutputStream As New java.io.FileOutputStream(strDestinationDirectory.Replace("\", "/") & oZipEntry.getName()) While 1 = 1 Dim iLen As Integer = oZipInputStream.read(sbBuf) If iLen < 0 Then Exit While oFileOutputStream.write(sbBuf, 0, iLen) End While oFileOutputStream.close() End If End While oZipInputStream.close() oFileInputStream.close() Catch ex As Exception Throw New Exception(ex.Message) End Try End Sub End Class

I tried to code above, including adding the reference to vjslib.dll into the script task. When I try to run, I get the following error: Could not load file or assembly 'vjslib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. The system cannot find the file specified. Is there something I missed doing? Frank

The error sounds like the code is correct because when it tries to do the IMPORTS java.util.zip, it cannot find it. I don't know what the Culture or PublicKeyToken has to do with anything -- something in the internals or security I'd guess. We are currently using VS 2005 with Framework 2.0. VS 2005 with framework 3 or 3.5 should work as well. Here's how Iwould troubleshoot the problem: Go into VS 2005 and open your Intergration Services project. Open your script object. Once the script development tool opens, find the PROJECT EXPLORER pane. You may have to click View --> Project Explorer (CTRL-ALT-L) to get it to show up. Expand the tree to display all your references -- it should list vjslib. (I also have Microsoft.SqlServer.ManagedDTS, Microsoft.SqlServer.ScriptTask, Microsoft.Vsa, System, System.Data, and System.Windows.Forms -- these loaded automatically.) If it does not appear, try making a reference to it again. Sometime the programming environment is flaky. If it's reference is listed, then... Delete the reference Save the file Try to run again. See if it is the same error. The same error means the reference is somehow bad Add the reference back in Save the file Test again. If it fails, create a new project and trythe same thing in a fresh project. If a fresh project (pretty much a duplicate of the first) still acts as though there is something wrong with the referenced dll, then try re-installing the framework (or try a fresh copy of the DLL from another developer PC). That's how I'd troubleshhot the problem. If you have another developer PC to test on, you might even try that route first. Good Luck -- I'd like to know what you find.

There is a third party control you can use http://www.cozyroc.com/

I had to opt for the jave code because our client does not have any third party software approved for use on the server to perform the 'unzip' task. They have Winzip approved for client machines, but the SSIS package works on the server. With that said, I am a proponent to buying third party software if it is an advantage in cost and quality. I don't want to re-invent the wheel when I can buy one at the local tire store. For instance, we are using Telerik controls for our ASP .NET screens.

install Visual J# 2.0 redistributable package to correct the error.

Just had the same error, and it had nothing to do with J#.Folder structure within the zip must exist in order for the extraction to take place.so if your file in the zip is: \private\BDA\BDA.xlsthen wherever you extract it to must have the subdirectories: \private\BDA\

That's way more complex than it needs to be.The only thing you might need to use the script task for is to set variables you can't otherwise do in the package itself. Execute Process task is the perfect tool for the job. We use it here to zip, encrypt extracts before shipping them off. The only gotcha is that if you've got a lot of switches after the exe, test them in a command prompt before you run your package. It will save you a lot of time.

I tried to code above, including adding the reference to vjslib.dll into the script task. When I try to run, I get the following error: Could not load file or assembly 'vjslib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. The system cannot find the file specified. Is there something I missed doing? Frank I got the same kind of error the first time... had installed a 32 bit version of Visual J# Redistributable Packages on a 64 bit machine. You can find the correct version here. And a C# version of that script here.

video converter to avi 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99

Guys, I am not good in VB and still learning. Any one with a codes to : 1. Script to unzip/unrar document 2. Script to load/append excel/CSV/text file/s into a table 3. Script to go search within a folder if there is any new file (excel/CSV/text) added. Am struggling and the library for vjslib, where can i get it? Only found version 1.0.0.0.0