Tim Mitchell
Follow Tim Mitchell on Twitter  Like Tim Mitchell on Facebook  Subscribe on YouTube  Connect on LinkedIn  Subscribe to the Data Geek Newsletter

Unzip Files with SSIS

Unzip Files with SSISIn my last post I shared how to build a package in SSIS to perform simple file archive (zip) operations. In this post, I’ll show you how to go the other direction and unzip files with SSIS.

Unzip Files with SSIS

Similar to the file zip operation, this solution uses the following tools:

  • 7Zip: This free utility manages the creation and extraction of compressed archive files. In the solution below, I used the portable command-line version.
  • SSIS Script Task: The script task manages all of the settings for executing the 7Zip operation.

For maximum reusability, I recommend parameterizing settings that will change over time. For this solution, I have parameterized the following three values:

  • 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.

image

In addition to the parameters above, I have set up three SSIS variables to capture runtime output from the 7Zip operation for easier logging and troubleshooting.

  • 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)

Script Task

As noted, an SSIS script task manages the execution of the 7Zip unzip operation. The three parameters above are used as read-only inputs to the script, while the variables shown are added as read-write variables to the script to allow capturing of the runtime values from standard input, standard output, and the exit code.

The code for the unzip operation is broken into sections below. The first section below creates C# variables from the SSIS parameter values, and builds the argument string that will be passed into 7Zip.

From here, the code largely resembles that used by the original zip operation. Below the script invokes the 7Zip executable with the parameters set above, capturing the output and exit code along the way.

Next, the code performs error checking. If there were error messages or the exit code was anything other than 0, the script will log the error and force a failure.

That’s it! With the above logic in an SSIS script task, you can easily unzip files with SSIS. So you don’t have to reconstruct the above snippets, I’ve included the download to the full project (which includes the package to unzip files with SSIS as well as the one to zip files from the earlier blog post).

Note that we’re not using any of the advanced features of 7Zip, of which there are many; if you need more flexible zip/unzip operations, the script task can be easily modified to take advantage of the many other settings in the 7Zip command line utility.

Conclusion

This post has demonstrated how to get around the lack of a file unzip utility in SSIS by creating our own using native SSIS functionality and the free 7Zip utility.

About the Author

Tim Mitchell
Tim Mitchell is a business intelligence and SSIS consultant who specializes in getting rid of data pain points. Need help with data warehousing, ETL, reporting, or SSIS training? Contact Tim here: TimMitchell.net/contact

3 Comments on "Unzip Files with SSIS"

  1. Mohammed Nazim Feroz | September 26, 2017 at 10:41 am | Reply

    Thanks for the amazing post!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: