Execute Process Task – Compress File Using 7-Zip

Abstract

In this blog post I will demonstrate using SQL Server Integration Services‘s Execute Process Task to compress a csv file into a zip format using 7-Zip.

Requirements

Article

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.

Sifiso.

3 Comments

  1. Thato says:

    Hi Sifiso,

    I would like to pass the name to the argument as a variable no luck thus far, any idea how I can do it.

    the idea is that I want to run this through a for each loop task so that it can repeat the process for multiple files

1 Trackbacks

Leave a comment