Create ZIP Files in SSIS

Of the very many things SQL Server Integration Services does well, one of its shortcomings is that it does not have a built-in way to create compressed archive (ZIP) files. Generating such files is a common need in ETL processes, so it’s surprising that the fifth generation of SSIS still does not have a native task to manage this operation. However, I’ve got a go-to workaround that will allow for easy generation of ZIP files in SSIS.

In this post, I’ll share how to create ZIP files in SSIS.

Create Zip Files in SSIS

To create ZIP files in SSIS, I recommend the following tools:

• 7Zip: This is a free utility, licensed under the liberal GNU-LPGL, that manages the creation and extraction of compressed archive files. 7Zip is a mature and well-documented tool, and it can be invoked from the command line, making it an easy choice for this task. I recommend the standalone console version, as it requires no installation and is therefore very portable.
• SSIS Script Task: I use the script task to invoke 7Zip with the necessary parameters to create an archive file. Although you could also use the Execute Process Task for this function, using a script allows for more options for logging and error handling than are provided in the Execute Process Task.

I recommend making the Zip operation a modular process, which means building it as generically as possible so that it can be reused. Therefore, I recommend externalize the values that are likely to change by creating SSIS parameters similar to the following:

• pPathTo7Zip: A package parameter, typed as a String, that points to the location of the 7Zip command line executable.
• pSource: A package parameter, typed as a String, that indicates the source for the archive file that will be created. This can be a file or a directory.
• pDest: A package parameter, typed as a String, that indicates the destination archive (ZIP) filename.

Each of these values can be specified when the package is executed, making it a more reusable ETL function.

In addition, the following package variables will be used for interim processing:

• vStdOutput: A String variable to capture the output from the 7Zip operation
• vStdError: A String variable to capture the error output (if any) from the 7Zip operation
• vExitCode: An Int32 variable to capture the output code from the execution of the 7Zip command line utility (we’re looking for 0 to indicate success)

With the parameters and variables in place, it’s time to create some code. My solution uses an SSIS script task configured to use the package parameters as read-only values, and each of the three variables listed above as read-write values.

Within the Main() function (which is automatically created when the script task is opened), I implement the following nuggets.

In the first snippet below, I do some prep work by assigning SSIS variables to local C# variables, and setting up the arguments to pass to the 7Zip executable.

Next, I set up a try/catch block that contains most of the execution logic. In the try{} block, I pass in the values to start the 7Zip executable process, including the suppression of the console window that would normally appear. Standard output and standard error outputs are written to string values, and the exit code is also captured. Upon success, an entry is made to the SSIS log reporting the information passed to the standard output variable. If an error occurs, I capture that error message in a string variable.

After the above execution attempt, I check for error conditions. If the exit code is anything but zero, or if the exception message or standard error contain a non-blank message, I consider the operation to have been a failure. In such a case, the error message (if any) is written to the SSIS log and the package fails.

Finally, I include the standard SSIS success operation for when no error condition exists.

Although not elegant or feature-rich, this is a very simple way to create ZIP files in SSIS. This logic could be made as complex as necessary to support more options than what I have shown here. You can download the full project here.

Conclusion

Integration Services does not include a native tool to generate compressed archive (ZIP) files. However, by using the free 7Zip utility and the built-in SSIS script task, it is easy to roll your own ZIP file creation package in SSIS.