SSIS – Unpack a ZIP file with the Script Task

A while ago I needed to unpack a couple of zip files from SSIS. There is no Microsoft SSIS task that contains this functionality so I searched the Internet. It seems that there are quite some third party tools that offer this functionally. It's also possible to download custom SSIS tasks. I personally always try to avoid third party tools and custom tasks so I searched on. It seemed there is a way to unzip files from SSIS with the Script Task. With some Visual Basic code using the Visual J# Library you can do the job. In this blog post I will use a Foreach Loop Container to loop through a folder that contains multiple zip files and unzip them one-by-one.

Make sure you have the Microsoft Visual J# Redistributable Package installed because a reference to vjslib.dll (Visual J# Library) is needed in the Script Task. Download it here for free.

Drag and drop a Foreach Loop Container on the Control Flow and create three variables with scope on the Foreach Loop Container:

Now only one thing needs to be done, add a reference to vjslib.dll (Visual J# Library):

&

Your unzip solution is ready now! For testing purposes you can uncomment the following line in the script to see the file name of each processed zip file in a message box at runtime:

'MsgBox("Current File: " & Dts.Variables("FileName").Value.ToString)

You can use this solution in many ways, for example, I used it in the solution below where I download multiple zip files from an FTP. These zip files contain CSV's that are used as source for the loading of a data warehouse.

Your method works brilliantly for us, even extended this by making this a parameters driven callable package.

However, we now need to do the same thing for an external file that is supplied to us in .GZ format. Is there a similar java or other utility we can call from SSIS to unzip these types of files? May well need to deal with 7zip file soon as well.

And before anyone assks, no we cannot request the format to be changed, as this is the standard format the data ias delivered in, and we are expected to handle it. We can do it manually, but that sort of defeats the whole purpose of "automating" the loading process.

Good lord Jorg, these guys writing in reply to your extremely helpful article are freaking LAZY!!

They should use this as a sample for what's possible in SSIS and configure what you've provided to use in their own environment...

I'm so used to seeing this when we interface with typical IT folks where everything has to be spoon fed... Try paying attention and LEARNING what Jorg did instead of looking to take his guide and using it without any idea of what's going on...

I used this solution. it is working brilliantly in my development environment but, when I move it to the testing environment and run it using a scheduler, it is throwing an error which is indicated below:

Error: 2015-06-22 09:46:02.04

Code: 0x00000002

Source: SCT UNZIP FILE

Description: The script threw an exception: Exception of type 'java.io.IOException' was thrown.