A common task if you are sending data over the line is to compress the files to minimize bandwidth and file storage. In this article I am going to show you how to use Winrar to zip up the contents of a folder using the Execute Process Task in a SSIS package.
Step By Step Walkthrough
Step 1: Fire up BIDS and create a new Integration Services Project as shown in figure 1 below.
Figure 1: Create a new Integration Services Project
Step 2: Once you have your project created, a package named Package.dtsx will already be added to your solution. You can rename that package by slowly double clicking it or by RIGHT CLICKING and then choose RENAME as shown in figure 2. I have appropriately named mine After you rename it, you will be asked if you want to rename the package object as well. Choose Yes. This step is optional, so skip renaming your package if you wish to do so!
Step 3: Now that we have our package renamed we are ready to zip up some files. Open up your toolbox if it is not already being displayed. You will find the toolbox under the View menu option. While the Control Flow tab is selected, you will see many tasks that allow you to accomplish many things. One of these tasks is called the Execute Process Task. Go ahead and drag one of these over onto your Control Flow design surface. If you have been following along, your project should look something like the one shown in figure 3.
Figure 2: Rename Your Package
Figure 3: Package Overview
Step 4: Now RIGHT CLICK on your Execute Process Task and select Edit
Step 5: Click on the Process tab
Step 6: Click on the ellipsis and navigate to your WinRAR install directory. Now you could go and hunt the WinRAR executable down on your system, but I chose the easy route and found out where the shortcut under the Start Menu\All Programs\WinRAR was pointing to. For my installation (default), the path was “C:\Program Files (x86)\WinRAR\WinRAR.exe” as can be seen in figure 4 below.
Step 7: Set the arguments of the executable (in this case the WinRAR.exe. I am setting mine to archive the files (a), create a zip file (-afzip) and to include subdirectories (-r) followed by the name that you want the zip file to have and the files to include in your zip file. My folder doesn’t have any, so I could have left this option out. So, the arguments look like this:
A –afzip –r ZippedFiles.zip *.txt
Step 8: Set the path of the files that you want to zip up (also shown in figure 4 below). In this case I have specified a folder within another folder on my desktop.
Figure 4: Execute Process Task – Process Tab
Now, you could have declared some variables set some of the values here, but I just wanted to quickly show you how you could get this up and running very quickly. Also note that I set the WindowStyle to Hidden to prevent a brief viewing of the file being generated. If all goes well, your zip file should be created in the path you stated in your arguments.
In summary, one could use this process to zip up files and then ftp them to a server or archive them on some file server somewhere. I sincerely hope that you have enjoyed this article. If you have any questions, please do not hesitate to post a comment.
Until next time, “keep your ear to the grindstone” – Good Will Hunting
Brian K. McDonald, MCDBA, MCSDBusiness Intelligence Consultant – Pragmatic Works Consultants
Email: bmcdonald@pragmaticworks.com | Blog: BI Developer Network
Convert with DTS xChange | Develop with BI xPress | Process with TaskFactory | Document with BI Documenter
This is really great information to have. The bubbles are cool too!
There is the really cool add on called Task factory that can do all this compression and decompression too. It is at pragmaticworks.com
cool tip, thanks..
Thank you huslayer, Mike and of course Sherri too! As Mike mentioned, Task Factory has a bunch of awesome custom add on's for SSIS! But for those who cannot talk their management into helping you save massive amounts of time for a very respectable price, this is just an example of using WinRar. :)
Hi McDonald,
Thank You! for this wonderful solution.