Recently I have been involved in a project whereby I have been required to extract data into CSV files, compress the files into a zip format and email the newly compressed files. This blog post is the first of several posts to come whereby I will be demonstrating how to fulfill such a requirement.
Let\’s begin by launching Microsoft Visual Studio and creating a new Integration Services Project which is located under Business Intelligence Projects category. (If you are using SQL Server 2012 or later, you will have to launch SQL Server Data Tools – SSDT).
After you have assigned a project name, proceed to click and drag the Execute Process Task (EPT) into the Control Flow pane from the toolbox (SSIS Toolbox in SSDT).
Right-click EPT and click on “Edit”
Under the General Tab, you can assign relevant Names & Descriptions. In this case, I have decided to name the task “EPT – Compress File” whilst the Description remains unchanged.
In the Process Tab, the EPT properties must be set as follows:
- RequiredFullFileName : True
- Executable : C:\\Program Files\\7-Zip\\7zG.exe
- Arguments: a “SIFISO_Test.zip” “SIFISO_Test.csv”
- WorkingDirectory: C:\\Test\\Excel\\20120930\\
- FailTaskIfReturnCodeIsNotSuccessValue : True
- SuccessValue : 0
- Timeout : 0
- WindowStyle : Hidden
Basically what we have done above is, among other things, set the Executable property to the .exe location of the compression software we are using; set the Arguments such that file “SIFISO_Test.csv” is compressed into “SIFISO_Test.zip”; specified the working directory where the CSV file will be located and zip file created; hid the exe window that appears when the file is being compressed.
It’s that simple.
Till next time folks, cheers.