Is there a way to unzip files in a SQL Server 2005 SSIS package. I now I can do it using winzip and executing the procedure, master..xp_cmdshell, but that is not what I am preferring at the moment. Anything direction in this regards will be much appreciated. Thanks, Monisha

There's nothing built-in to SSIS to do it. The way to achieve this is to call out to an external process. By the way, don't use xp_cmdshell to do this, there's no point. Cut out the middle man and use the Exeucte Process Task instead. -Jamie

There are plethora of command linedecompress/decompression utilities available as candidates to unzip files in an execute package task, as noted. http://unxutils.sourceforge.net/UnxUtils.ziphas some freely available native Win32 ports of zip/unzip and gzip/gunzip. Onecan also use the native .NET compression functionality as well (whichsome may consider adding in a middle man, but nonetheless...)Below is a script task whichdecompresses ofgzip files. Change it from GzipStream to ZipStream for zip files, as well as modifying the extension its expecting from ".gz" to ".zip". Imports System Imports System.IO Imports System.Data Imports System.Math Imports System.Text Imports System.IO.Compression Imports Microsoft.SqlServer.Dts.Runtime '''<summary>Decompress file</summary> Public Class ScriptMain Public Sub Main() Dts.TaskResult = Dts.Results.Success Dim success As Boolean = True Dim workFilePath As String workFilePath = Dts.Variables("CompressedFilePath").Value.ToString() If File.Exists(workFilePath) Then If Not workFilePath.EndsWith(".gz") Then Dts.Events.FireInformation(0, "", workFilePath + " is not compressed; skipping decompression", Nothing, -1, True) Return End If Dim uncompressedFileName As String Dim bytes(Int16.MaxValue) As Byte Dim n As Integer = 1 Try uncompressedFileName = workFilePath.Substring(0, workFilePath.Length - 3) Dts.Events.FireInformation(0, "", "decompressing " + workFilePath + " to " + uncompressedFileName, Nothing, -1, True) Using writer As New FileStream(uncompressedFileName, FileMode.Create) Using compressedStream As Stream = File.Open(workFilePath, FileMode.Open, FileAccess.Read, FileShare.None) Using unzipper As New GZipStream(compressedStream, CompressionMode.Decompress) Do Until n = 0 n = unzipper.Read(bytes, 0, bytes.Length) writer.Write(bytes, 0, n) Loop unzipper.Close() End Using compressedStream.Close() End Using writer.Close() End Using Catch ex As Exception Dts.Events.FireError(0, ex.TargetSite().ToString(), "Unable to decompress " + workFilePath + "; "+ ex.Message, Nothing, -1) success = False Finally If success = False And File.Exists(uncompressedFileName) Then Dts.TaskResult = Dts.Results.Failure File.Delete(uncompressedFileName) End If End Try Else Dts.Events.FireError(0, "", workFilePath + " does not exist", Nothing, -1) Dts.TaskResult = Dts.Results.Failure Return End If End Sub End Class

Monisha, I have had luck with the component at the link below. It is not a full featured as shelling out to command line as Jamie recommends, but if you are dealing with only one file it does a good job. http://www.thejoyofcode.com/SSIS_Compress_File_Task_now_with_added_decompression.aspx

Hello jaegd, I am working with the same task that includes downloading the zip file from the http location and decompress and read the internals. Now the code example you provided is relevant to the .gz file format. I haven't checked with ".gz" but for sure the solution you mentioned for ".zip" file doesn't seemed to be their at all. Their is no zipstream class to handle the file type ends with .zip. If you have already used this file format could you please help me with this one. That will be worth a million. Regards Sandesh Kadam jaegd wrote: There are plethora of command linedecompress/decompression utilities available as candidates to unzip files in an execute package task, as noted. http://unxutils.sourceforge.net/UnxUtils.ziphas some freely available native Win32 ports of zip/unzip and gzip/gunzip. Onecan also use the native .NET compression functionality as well (whichsome may consider adding in a middle man, but nonetheless...)Below is a script task whichdecompresses ofgzip files. Change it from GzipStream to ZipStream for zip files, as well as modifying the extension its expecting from ".gz" to ".zip". Imports System Imports System.IO Imports System.Data Imports System.Math Imports System.Text Imports System.IO.Compression Imports Microsoft.SqlServer.Dts.Runtime '''<summary>Decompress file</summary> Public Class ScriptMain Public Sub Main() Dts.TaskResult = Dts.Results.Success Dim success As Boolean = True Dim workFilePath As String workFilePath = Dts.Variables("CompressedFilePath").Value.ToString() If File.Exists(workFilePath) Then If Not workFilePath.EndsWith(".gz") Then Dts.Events.FireInformation(0, "", workFilePath + " is not compressed; skipping decompression", Nothing, -1, True) Return End If Dim uncompressedFileName As String Dim bytes(Int16.MaxValue) As Byte Dim n As Integer = 1 Try uncompressedFileName = workFilePath.Substring(0, workFilePath.Length - 3) Dts.Events.FireInformation(0, "", "decompressing " + workFilePath + " to " + uncompressedFileName, Nothing, -1, True) Using writer As New FileStream(uncompressedFileName, FileMode.Create) Using compressedStream As Stream = File.Open(workFilePath, FileMode.Open, FileAccess.Read, FileShare.None) Using unzipper As New GZipStream(compressedStream, CompressionMode.Decompress) Do Until n = 0 n = unzipper.Read(bytes, 0, bytes.Length) writer.Write(bytes, 0, n) Loop unzipper.Close() End Using compressedStream.Close() End Using writer.Close() End Using Catch ex As Exception Dts.Events.FireError(0, ex.TargetSite().ToString(), "Unable to decompress " + workFilePath + "; "+ ex.Message, Nothing, -1) success = False Finally If success = False And File.Exists(uncompressedFileName) Then Dts.TaskResult = Dts.Results.Failure File.Delete(uncompressedFileName) End If End Try Else Dts.Events.FireError(0, "", workFilePath + " does not exist", Nothing, -1) Dts.TaskResult = Dts.Results.Failure Return End If End Sub End Class

That's right, there is no ZipStream. To un-archive a zip file in a script task, you'd can add a reference to vjslib, and then use the Zip related classes in the java.util.zip namespace. "java.util.zip" doesn't mean you're using java or J# (script task doesn't support J# anyhow), but that "java.util.zip" is the "namespace" of the classes which can read/write zip archives. One would have to using the ZipFile, ZipEntry, and probably ZipOutputStream to write the compressed files though.

Give Zip Task a try. It works nice.

Hi dude..may be its very late reply..but someone will find very helpful. Using SSIS -Execute Process task, we can unzip the file. Steps: 1. Drag and drop execute Process task 2. Edit the component. 3.In the Process Menu, 'Executable' =C:\Program Files\WinZip\WINZIP32.EXE 'Arguments'= -e -o "fileNameinZipformat.zip " Working Dir='C:\TestFolder ' 4. Arguments tips: -e extract -o OverWrite exist Thats it..you can run the Package to unzip the file.. WIZ ZIP Command Line: Ref: http://www.memecode.com/docs/winzip.html WinZip supports command line options to add and extract from files. Be sure to read the Notes section below for additional important information. Adding Files The command format is: winzip32 [-min] action [options] filename[.zip] files where: -min specifies that WinZip should run minimized. If -min is specified, it must be the first command line parameter. action-a for add, -f for freshen, -u for update, and -m for move. You must specify one (and only one) of these actions. The actions correspond to the actions described in the section titled "Add dialog box options" in the online manual. options-r corresponds to the Include subfolders checkbox in the Add dialog and causes WinZip to add files from subfolders. Folder information is stored for files added from subfolders. If you add -p, WinZip will store folder information for all files added, not just for files from subfolders; the folder information will begin with the folder specified on the command line. -ex, -en, -ef, -es, and -e0 determine the compression method: eXtra, Normal, Fast, Super fast, and no compression. The default is "Normal". -hs includes hidden and system files. Use -sPassword to specify a case-sensitive password. The password can be enclosed in quotes, for example, -s"Secret Password". filename.zipSpecifies the name of the Zip file involved. Be sure to use the full filename (including the folder). filesIs a list of one or more files, or the @ character followed by the filename containing a list of files to add, one filename per line. Wildcards (e.g. *.bak) are allowed. Extracting Files The command format is: winzip32 -e [options] filename[.zip] folder where -e is required. options-o and -j stand for "Overwrite existing files without prompting" and "Junk pathnames", respectively. Unless -j is specified, folder information is used. Use -sPassword to specify a case-sensitive password. The password can be enclosed in quotes, for example, -s"Secret Password". filename.zipSpecifies the name of the Zip file involved. Be sure to specify the full filename (including the folder). folderIs the name of the folder to which the files are extracted. If the folder does not exist it is created.

FYI: In the arguments property, zip file name should be entered with diuble quotes. 'Arguments'= -e -o "fileNameinZipformat.zip" Thanks

Hi There, I have a situation as such. Need to loop over a group of file in folder C:\A Store filenames in variable USER::FileName Pass the var to the Execute Process Task [Winzip32.exe] When executing I get a message that says USER::FileName.zip not found & and This is how I have set my argument -e "User::fileName". And Yes I do have a .zip file in folder C:\A !!!! Do guide me thru.Thank You Very Much

the argument should be an ssis expression: Code Snippet "-e " + @[User::fileName] hth

Hi, Did you able to pass a variable to argument? I am having same requirment. Please let me know what have you done exactly? Thanks Raj

It would be easier and faster to do like jamie said. I see someone already said WinZip however I prefer the free 7-zip (free for commercial as well).If you want to do it in code you can look at the below link example, however I suggest, like Jamie, use the Execute Process task described later.The following shows a way to do it in code and also the http://www.7-zip.org website also has some developer samples as well.http://geekswithblogs.net/robz/archive/2008/09/23/sharpziplib-versus-7-zip-for-large-file-sets.aspxTo use the Execute Process way (**preferred way**) the following will help you with the command args of 7-zip. Note: currently i use 7-zip to decompress 182 GZip (*.gz) files totaling 392 MB compressed to a decompressed total of 2.8 GB in less than a minute. (dual quad core processors and 64bit)7z e archive.zip -oC:\soft *.cpp -r7z: executablee: use extract commandarchive.zip: source archive you want to extract from-oC:\soft: the destination folder (-o is the switch and C:\soft is the argument)*.cpp: only extract cpp files (C++)-r: traverse all subdirectoriesSource: http://dotnetperls.com/Content/7-Zip-Examples.aspx

You can download BI xPress from Pragmaticworks. And they have feature called Script Snippet. It will show you how to Zip/Unzip files without Buying 3rd Party Tools like WinZip ...it supports .zip formathttp://www.pragmaticworks.com/Products/Business-Intelligence/BIxPress/If you dont want to use script task and wants to perform High performance compression then try Task Factory... its the fastest SSIS Zip Solution.http://www.pragmaticworks.com/Products/Business-Intelligence/TaskFactory/

I recommed using this way of unzipping file using SSIS : http://sqlblogcasts.com/blogs/jorg/archive/2009/08/27/ssis-unpack-a-zip-file-with-the-script-task.aspxA Visual J# library has been used to unzip the file. The benefit is VJ# comes as a free re-distributable package, the library is of managed code and comes from Microsoft.--Siddharth Mehta http://siddhumehta.blogspot.com

HI, Its not working for me... I have give variable value to "c:\vvv.zip" and Arguments = " -e -o " + @[User::Filename].. Executable - "C:\Program Files\WinZip\WINZIP32.EXE" Please do let me know if I am missing Something

This is an old post, but in case anyone gets to hear and has issue (like I did) You don't want to put the arguments directly into the "Arguments" of the task, instead, go to the Expressions and supply a value to the Arguments there. Hope this helps someone.

Here is an example to unzip ZIP files (not gzip) with a script task: http://microsoft-ssis.blogspot.com/2011/01/unzip-files-with-ssis.html It uses the Microsoft Visual J# Redistributable Packages (vjslib.dll).

THANK YOU SO MUCH richardbjr!!!! I could not for the life of me figure out how to get this to work. so for anyone out there stuck like i was, here are explicit instructions on how to make it work. In the Execute Process Task Editor, on the Process section for "Executable", enter your zip program, like below c:\program fles\winrar\winrar.exe leave "Arguments" blank in this section for "WorkingDirectory", enter in the directory you want to extract the file to c:\myfolder -- Then move to the 'Expressions' tab. Under Misc, open up the Expressions box, then for Property, select Arguments, then for Expression, enter in your arguments, like I did below "e " +@[User::whatever_variable] + " -o+" -- that was the key step that i was missing. Thanks again Richard, you really helped me out tons, i've been racking my brain trying to figure this out. kept entering it in the wrong spot and the unzipping program would always pop up on me.

THANK YOU SO MUCH richardbjr!!!! I could not for the life of me figure out how to get this to work. so for anyone out there stuck like i was, here are explicit instructions on how to make it work. In the Execute Process Task Editor, on the Process section for "Executable", enter your zip program, like below c:\program fles\winrar\winrar.exe leave "Arguments" blank in this section for "WorkingDirectory", enter in the directory you want to extract the file to c:\myfolder -- Then move to the 'Expressions' tab. Under Misc, open up the Expressions box, then for Property, select Arguments, then for Expression, enter in your arguments, like I did below "e " +@[User::whatever_variable] + " -o+" -- that was the key step that i was missing. Thanks again Richard, you really helped me out tons, i've been racking my brain trying to figure this out. kept entering it in the wrong spot and the unzipping program would always pop up on me.