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 configure the Foreach Loop Container:
– Enumerator: Foreach File Enumerator
– Files: *.zip
– Retrieve file name: Name and extension

Next click on the + next to Expressions add the following expression to connect the SourceFolder variable to the Directory property of the Foreach Loop Container:

Now go to the Variable Mappings and select the FileName variable on Index 0. Doing this we will be able to access the current file name when the Foreach Loop Container enumerates the zip files.

Now drag and drop a Script Task on the Control Flow, inside the Foreach Loop Container:

Open the Script Task Editor and do the following:
– Set the ScripLanguage on: Microsoft Visual Basic 2008
– Select our three ReadOnlyVariables using the new SSIS2008 Select Variables window:

Now click Edit Script and copy/paste the following script:

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports java.util.zip

    Public Sub Main()

        Try

            Dim strSourceFile As String
            Dim strDestinationDirectory As String

            ‘MsgBox(“Current File: ” & Dts.Variables(“FileName”).Value.ToString)

            strDestinationDirectory = Dts.Variables(“DestinationFolder“).Value.ToString 
            strSourceFile = Dts.Variables(“SourceFolder“).Value.ToString & Dts.Variables(“FileName“).Value.ToString

            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

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.