Unzip files with SSIS

Case
My source files are zipped and I want to unzip them within the SSIS package.

Solution
There are a lot of options to unzip files within SSIS. For example:

• Third party, open source or my own unziptasks
• Execute Process Task in combination with Winzip commandline
• Script task with opensource zip dll
• Script task with System.IO.Compression (works only for gzip)
• Script task with Visual J# Redistributable Packages (works only for zip) 

* Update: my own unzip task *

 

 

 

 



This solution uses the Microsoft Visual J# Redistributable Packages (don't worry you don't have to write J#), because it's free and you don't have use a third party or opensource dll on a production server which is often a big deal.

1) Visual J# Redistributable Packages
Install Microsoft Visual J# Redistributable Packages on your machine(s). You can download the correct version (x84, x64, IA64) at Microsoft.

2) Variables
Create two String variables named ZipFile and UnZipFolder to store the zip filename and the unzip folder.

Two String variables

3) Loop through folder with zip files
Create a foreach loop that loops through a folder and looks for zipfiles.

Foreach (zip)file enumerator

4) Map variable
Map the ZipFile variable to index 0 in the Foreach Loop.

Variable Mappings

5) Script task with variables
Create a Script task within the Foreach loop and select the two variables as ReadOnly.

ReadOnlyVariables

6) Unzip with Script task
Add a reference to the Microsoft Visual J# Redistributable Packages dll in your script. The name of the .NET dll is vjslib. After you have added it, it shows on the right site (3).

In Project menu, Add Reference...

After that add the following code (method and usings)

// C# code
using System;
using System.Data;
using System.IO;        // Added
using java.util.zip;    // Added
using java.io;          // Added
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;

namespace ST_2651c39f95ad4031897db646b9d61275.csproj
{
    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {

        #region VSTA generated code
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion


        public void Main()
        {
            // Variables for zipfile and unzip folder
            String zipfilePath;
            zipfilePath = Dts.Variables["ZipFile"].Value.ToString();
            String unZipFolderPath;
            unZipFolderPath = Dts.Variables["UnZipFolder"].Value.ToString();

            // Two variables needed for unzipping
            sbyte[] buf = new sbyte[1024];
            int len;

            // Filestreams for the zipfile
            FileInputStream fis = new FileInputStream(zipfilePath);
            ZipInputStream zis = new ZipInputStream(fis);
            ZipEntry ze;

            // Loop through all files in the zipfile
            while ((ze = zis.getNextEntry()) != null)
            {
                // File name format in zip file is:
                // folder/subfolder/filename
                string fileName = ze.getName();

                // Get the filename out of the complete path
                int index = fileName.LastIndexOf('/');
                // If file
                if (index > 1)
                {
                    // Determine directory
                    string folder = fileName.Substring(0, index);
                    DirectoryInfo di = new DirectoryInfo(unZipFolderPath + folder);

                    // Create directory in UnZipFolder if not exists
                    if (!di.Exists)
                    {
                        di.Create();
                    }
                }
                // Extract file
                java.io.FileOutputStream fos = new java.io.FileOutputStream(unZipFolderPath + fileName);
                while ((len = zis.read(buf)) >= 0)
                {
                    fos.write(buf, 0, len);
                }
                fos.close();
            }
            zis.close();
            fis.close();

            Dts.TaskResult = (int)ScriptResults.Success;
        }
    }
}

5) Continuing...
Now add your own tasks to continue the Control Flow. It could look something like this.

Loop through unzipped
files and do something

Note: This is a very basic script. You can add tons of checks and other features to make sure the script always works. Let me known if you have any questions. Zipping of files will be handled in another article.

Download the SSIS 2008 example package.