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 a 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 create 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 Control Flow pane from toolbox (SSIS Toolbox in SSDT).
Right click EPT and click on “Edit”
Under the General Tab, you can assign relevant Name & Description. In this case I have decided to name the task as “EPT – Compress File” whilst 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 compressision 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.