Zip utility through SSIS

Hello there! . In this post we will discuss how we can zip files available in different folder structure using SSIS.

Prerequisite:

• Licensed version of Winzip 32 software

Approach:

Zipping of files can be done by loads of software such as Winzip, 7-Zip, Peazip, Zip995 etc. The primary motive is to initiate the zipping software through command lines in SSIS and it can be achieved by using either the Execute process task or Script task. For this post we will be using Winzip 32 which is the most admired among the zipping software.

Now this might be surely bothering you that when we can select all files at one go and then just zip it so what’s the use of a zip file utility through SSIS. So let us consider a situation where various reports are dumped in different folders through an automated application and the need is to zip the reports in respective folders and the zip file should also be named as per the folder names. Above is just a basic example where SSIS can save you but in real situations the requirement can be more complex and SSIS can be utilized in those situations.

Let us create a folder structure with some files inside it. For this post I have created around 11 folders which contains some excel files and we will zip the excel files inside each folder and name the zip files as per the folder names. Below is the screenshot of the folder structure.

In SSIS we will use a for each loop container to run the utility for each folder and zip the files inside it. For initiating the zip files we will make use of Execute process task which will initiate the winzip through command line statements. Below is the configuration required for for-each loop container.

For each loop container:

Collection page:

Enumerator: Foreach File Enumerator

Folder: It is the parent folder in which the individual folders resides. For us the Olympics folder contain all the individual folders i.e. all the country folders. So it is C:\Suvendu\Olympics.

Files:  *.xlsx as we have excel files. If a search is required based on the file names then the file expression can also be changed. For e.g. if only excel files starting with ABC needs to zipped then the value will be ABC*.xlsx

Retrieve Fully Name: Fully Qualified

Traverse Subfolders: Enable

Variable Mappings:

As we need the full file path for the folders and excel files we will store it in a string variable so that it can be accessed by execute process task.

Now drag and drop an execute process task inside the for each loop container. Below are the configurations for execute process task.

Execute Process Task:

Process:

RequireFullFileName: True

Executable: Location of the executable. C:\Program Files\WinZip\WINZIP32.EXE

Arguments: We will set up the command lines for initiating the winzip utility here.  It will be configured through expression so we will cover it in more details in expressions page.

WorkingDirectory: It specifies the directory where the zip files needs to be created.

All other properties should be left as it is.                    

Winzip command line parameters

Before moving to expressions page let us get a brief background on winzip command line utility. Below is the command line for initiating and executing the winzip.

The command format is:

    [-min]  action  [options]  filename[.zip]  files

 where:

-min specifies that WinZip should run minimized. If -min is specified, it must be the first command line parameter.

Action
-a for add, -f for freshen, -u for update, and -m for move. You must specify one (and only one) of these actions. The actions correspond to the actions described in the section titled “Add dialog box options” in the online manual.

Options
-r corresponds to the Include subfolders checkbox in the Add dialog and causes WinZip to add files from subfolders. Folder information is stored for files added from subfolders. If you add -p, WinZip will store folder information for all files added, not just for files from subfolders; the folder information will begin with the folder specified on the command line.

-ex, -en, -ef, -es, and -e0 determine the compression method: eXtra, Normal, Fast, Super fast, and no compression. The default is “Normal”. -hs includes hidden and system files. Use -sPassword to specify a case-sensitive password. The password can be enclosed in quotes, for example, -s”Secret Password”.

Filename.zip
Specifies the name of the Zip file involved. Be sure to use the full filename (including the folder). For e.g. if we want to name the zip file as the folder name and create the zip file inside the folders then we have to provide the full path along with the zip file name. The full path and the zip file name can be obtained from the variable assigned during the foreach loop container. In our case the variable assigned during for each loop container contained C:\Suvendu\Olympics\Australia\Australia1.xlsx as the first value which then needs to be modified to get the folder full path i.e. C:\Suvendu\Olympics\Australia and the zip file name i.e. Australia.

Files
Is a list of one or more files, or the @ character followed by the filename containing a list of files to add, one filename per line. Wildcards (e.g. *.bak) are allowed.

For more details please navigate to the below URL:

http://www.rondebruin.nl/parameters.htm 

Expression:

We have to set couple of properties from the expressions page i.e. Arguments and Working Directory. Below are the values:

Arguments: “-min -a “+  @[User::strIntermediateConnString] + @[User::strFolderNm]  +”.zip *.xlsx”

Where,

@[User::strIntermediateConnString] = C:\Suvendu\Olympics\Australia  for Australia folder

@[User::strFolderNm]   =  Australia

Working Directory: @[User::strIntermediateConnString]

Where,

@[User::strIntermediateConnString] = C:\Suvendu\Olympics\Australia\

SSIS Variables for this package:

We have used three variables for this package. They are:

strFolderConnectonString: Assigned during foreach loop container. It contains the full path till the excel file i.e. C:\Suvendu\Olympics\Australia\Australia1.xlsx

strIntermediateConnString: This is a derived variable from strFolderConnectonString to obtain the value till the last folder i.e. C:\Suvendu\Olympics\Australia\.

Expression value= Reverse( Substring(Reverse(@[User::strFolderConnectonString]),Findstring(Reverse(@[User::strFolderConnectonString]),”\\”,1),Len(@[User::strFolderConnectonString])))

strFolderNm: This is a derived variable from strIntermediateConnString to obtain the folder value i.e. Australia.

Expression value= Reverse(Substring( Reverse(Reverse(Substring(Reverse(@[User::strFolderConnectonString]),Findstring(Reverse(@[User::strFolderConnectonString]),”\\”,1),Len(@[User::strFolderConnectonString])))),  Findstring(  Reverse(Reverse(Substring(Reverse(@[User::strFolderConnectonString]),Findstring(Reverse(@[User::strFolderConnectonString]),”\\”,1),Len(@[User::strFolderConnectonString])))) , “\\”,1 ) + 1, Findstring(  Reverse(Reverse(Substring(Reverse(@[User::strFolderConnectonString]),Findstring(Reverse(@[User::strFolderConnectonString]),”\\”,1),Len(@[User::strFolderConnectonString])))) , “\\”,2 ) – 2 ))

 

It’s ShowTime now. We are all set to execute the package now. On executing the package,  zip files for each folder will be created inside the respective folders.

Do leave a comment if you like the post!!