Dynamically Zipping Files in SSIS

There are a number of posts and articles regarding zipping files in SQL Server Integration Services (SSIS). I have found it fairly easy to accomplish this task using an open source zip utility called 7-zip. This utility allows file compression to be executed from the command line, making it very easy to utilize the ‘Execute Process Task’ control flow. 7-zip allows packing and unpacking of ZIP, 7z, GZIP, BZIP2 and TAR and typically provides better compression than WinZip. In this post, I will explain how to dynamically zip files with dynamically defined connection managers and attributes.

First, create a variable at the package level called ‘DirectoryToZip.’  Set the scope at the ‘Package’ level, data type to ‘String’ and the value to ‘D:\SSIS\ZIP\’.  This will be the directory where you will copy files to be zipped.

01

Next, drag and drop a For Each Loop on to the Control Flow work pane.  Under the collection view, add an expression with the property set to ‘Directory’ and the expression equal to @[User::DirectoryToZip].  Set the files property to ‘*.*’ to capture all files in the specified directory and retrieve the file name and extension.

02

Under the variable mappings view, choose a new variable at the package level and set that equal to ‘FileName’.  This variable is going to capture the file name of each file in the directory enumerated by the For Each Loop.

03

Once that is complete, drag and drop an ‘Execute Process Task’ control flow item.  Set the executable equal to ‘C:\WINDOWS\System32\cmd.exe’.  This will open up the command line and allow you to pass machine commands from the arguments section.

04

Next, click on the expressions view and create two new expressions: Arguments and WorkingDirectory.

In the expression editor, copy and paste the below expression:

“C:\\WINDOWS\\system32\\cmd.exe” + ” /c D:\\SSIS\\7z.exe a ” + @[User::DirectoryToZip] +  @[User::FileName] + “.zip  ” + @[User::DirectoryToZip] +  @[User::FileName]

This will create an expression to call the executable 7-zip and create a zip file based on the naming conventions of the original file.

7-zip is an open source windows utility for manipulating archives.  You will need to download this utility and for the purposes of this demonstration, place the executable 7z.exe in D:\SSIS\. The download, documentation and more can be found at:

http://www.7-zip.org/

05

Once you have installed 7-zip and created the expression for the zip process, you will need to create 3 more variables:

ZippedFiles: @[User::DirectoryToZip] + “ZIPPED\\”

–          This will be your archive location.

MoveZipFiles: @[User::DirectoryToZip]+ @[User::FileName]+”.zip”

–          This will be used to move any ‘.zip’ files in the DirectoryToZip to an archive location.

DeleteFiles: @[User::DirectoryToZip]+ @[User::FileName]

–          This will be used to remove files from the file system once they have been successfully zipped

06

Next, create a File System task to delete files once they have been successfully zipped.  Set the file system task operation equal to ‘Delete file’, the source connection IsSourcePathVariable to ‘True’ and the Source variable to User::DeleteFiles (the variable you created in the previous step).

07

Once you have successfully zipped all of your files and deleted the originals, you will want to create a File System task to move the zip files from the ‘zip landing zone’ to an archive location.  Set the File System task operation to ‘Move’, the destination and source path variables equal to true, the SourceVariable to User::MoveZipFiles and the DestinationVariable to User::ZippedFiles.

08

Now create a sub-directory called ‘ZIPPED’ in your ‘ZIP’ directory.

09

Once that is complete, your package should resemble the package below and you will be able to dynamically compress files on your file system.

10

Advertisements

Tags: , , , , ,

8 Responses to “Dynamically Zipping Files in SSIS”

  1. Zip Utility Compression Test « data integrity Says:

    […] data integrity Integrity is the essence of everything successful. « Dynamically Zipping Files in SSIS […]

  2. Todd McDermid Says:

    You can also find some compression and decompression tasks at the SSIS Community Tasks and Components site (http://ssisctc.codeplex.com/) – no command line required.

  3. Slick Says:

    Wow what an article.. but no unzipping procedure? I was looking for the exact opposite! To use 7-zip to unzip :-(

  4. dataintegrity Says:

    Thanks for the feedback Slick. To outline it a bit clearer, the decompression procedure is embedded in an SSIS expression for flexibility.

    C:\\WINDOWS\\system32\\cmd.exe” + ” /c D:\\SSIS\\7z.exe a ” + @[User::DirectoryToZip] + @[User::FileName] + “.zip ” + @[User::DirectoryToZip] + @[User::FileName]

    To decompress via command line:

    D:\SSIS\7z.exe a D:\SSIS\ZIP\NameOfYourDestinationZipFile.zip D:\SSIS\ZIP\FilesToZip*.*

  5. Aaron Says:

    I was curious as to why you use cmd.exe instead of calling the 7z.exe? I was about to try this by calling 7zip directly but I figured there was a particular reason you preferred to use cmd.exe.

  6. Tai Says:

    Nice article, I’m currently zipping things up using Excel VBA and 7za.exe (doesn’t need to be installed, unlike 7z.exe) but my boss wants everything moved to SSIS so this is just the thing.

    I know that when you use 7-zip via Excel VBA, you have to use ShellAndWait in order to see if your command line succeeds (return value 0), otherwise Excel starts the command line running and then resumes immediately where it left off in the VBA.

    Does SSIS automatically wait for it to be done, and the execute process knows to fail if the command line failed?

    Thanks.

  7. dataintegrity Says:

    SSIS does wait for the process to complete using the Execute process task. If you see the third image has something similar to what you are taking about with a SuccessValue of 0. Once the process completes, it will move on to the next. Good luck with the SSIS conversion!

  8. Roner Says:

    Great article! Congratulations. I did a somewhat different, the Execute Process Task component, the Executable option, spent the address where it was installed 7-Zip (C:\Program Files\7-Zip\7z.exe) and the option of Expression arguments passed command of the 7-Zip (“a” + @[User::DirectoryToZip] + @[User::FileName] + “.zip ” + @[User::DirectoryToZip] + @[User::FileName]). It worked perfectly.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: