In 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.
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)
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.
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.