We have many scenario that we need to Zip many files which we come across and then so some operations like either sending it as a email or just moving zipped file to some other destinations etc.
But we were using manual method to zip multiple files. In this post, I tried to create a package which will zip multiple files using SSIS.
Here for Zipping files purpose, I'm using 7-ZIP which is free software available in google sites. Download files and install onto your system.
First let me show how to Zip on file and later I will show how to zip multiple files using SSIS and 7Zip tool.
Now to compress this file, I will open my SSIS and I'm dragging and dropping EXECUTE PROCESS TASK from Control Flow.
Now right click on Execute Process task and go for edit and select Process option.
In process tab, under Executable option, Browse for executable file where 7Zip is installed. I have installed in the path C:\Program Files\7-Zip\7z.exe
And under Arguments option, write following code:
Where C:\Documents and Settings\\Desktop\test\source\zipped.7z is where the file I'm zipping should reside and Name of compressed file should be Done.7z
NOTE: Instead of extension .7z, If we give .zip, then compressing file be in .Zip format and Similarly you can use .Rar format also to compress.
The snippet is shown below. (I deleted other compressed file I did using SSIS.)
Now under Enumerator Configuration option, Select Source name where our multiple files are residing.
under files option, keep it as *.* which actually indicates, fetch whatever files are available. (If we give *.txt, It means it is going to fetch all .txt files under any name and if we give say dhina.txt then it is going to fetch only file name dhina.txt)
Now select under Retrieve File Name option, select Name and extension which actually fetches only name and extension of file and sends to any variable we mentioned in variable mappings Tab of ForEach Loop container.
we know that value we need to fill is,
Since we need multiple files, the command should be altered with the help of variables we created.
since we are using up variables, we can actually Expression tab of Execute Process task.
select property as Arguments and copy paste these following expression. (We can actually analyze by replacing values the variable is possessing)
Then give ok and run the task.
C:\Programs Files\7-Zip\7z.exe
In Recent work, I came across a situation to UnZip files.
When I tried to parse arguments
e "<Zipped Source Folder Path>" "<Destination Path For Unzipping Folder>", Error was thrown to check the arguments.
So I quickly changed to by adding "-O" as it is shown above snippet which tells Set output Directory.
So my new Argument was something like this:
e "<Zipped Source Folder Path>" "<Destination Path For Unzipping Folder>" "<-o>".Still the error occurred. Whatever Changes we made in argument placing, the task was failing to Unzip the files.
Then We came across in some other task where Unzip Process was done and we had a look and We came across a Argument "-aoa"
Changes to be made for Unzipping Files:
1) Arguments: e "<Zipped Source Folder Path>" "-aoa"
2) Destination Path For Unzipping Folder: The destination folder where our unzip files should locate can be configured by its value at WORKING DIRECTORY option in Execute Process Task.
Example:
Say I have a Zip File in E:\ Drive and name of Zip file is ExcelToPDF.zip.
I want to unzip this File to Path E:\UnzipFiles\.
So I created Two variables, One for Holding Zipped File Path and Other for Unzipped folder path.
1) @[User::ZippedPath] = E:\ExcelToPDFSite.zip
2) @[User::UnzippedFiles] = E:\UnzipFiles\
So in Execute Process Task, I will change in following way.
In Arguments:
In Working Directory:
And Run the Package. Happy Unzipping!!!
But we were using manual method to zip multiple files. In this post, I tried to create a package which will zip multiple files using SSIS.
Here for Zipping files purpose, I'm using 7-ZIP which is free software available in google sites. Download files and install onto your system.
First let me show how to Zip on file and later I will show how to zip multiple files using SSIS and 7Zip tool.
Compressing Single file.
Here I'm trying to Zip one single flat file which is of 40MB size. I kept this file in C:\Documents and Settings\\Desktop\test\source folder.Now to compress this file, I will open my SSIS and I'm dragging and dropping EXECUTE PROCESS TASK from Control Flow.
Now right click on Execute Process task and go for edit and select Process option.
In process tab, under Executable option, Browse for executable file where 7Zip is installed. I have installed in the path C:\Program Files\7-Zip\7z.exe
And under Arguments option, write following code:
a "C:\Documents and Settings\\Desktop\test\source\Done.7z" "C:\Documents and Settings\\Desktop\test\source\txt1.txt
Where C:\Documents and Settings\\Desktop\test\source\zipped.7z is where the file I'm zipping should reside and Name of compressed file should be Done.7z
NOTE: Instead of extension .7z, If we give .zip, then compressing file be in .Zip format and Similarly you can use .Rar format also to compress.
Now Give ok and then Run EXECUTE PROCESS TASK.
We can see Done.7z compressed file is formed in directory we mentioned. The Snippet of all those are followed.
Now lets do it for Multiple files. Here I'm going to Add two more files named txt2.txt and txt3.txt just for example purpose in same Directory as mentioned earlier. ( C:\Documents and Setting\\Desktop\test\source)
Compressing Multiple files.To fetch multiple files we need FOREACH loop. So I have a FOREACH loop container and EXECUTE PROCESS task for operation.
To fetch files and hold, I'm creating a variable called Filename.
Since my Source and path is where my compressed end result file should reside is same, I'm using one more variable called Directory and value of Directory is C:\Documents and Setting\\Desktop\test\source.
Since I have only path right now in variable and since I need to save compressed file with some name and with its extension, I'm creating one more variable called Zipped and value of Zipped variable will be Zipped.7z
Here Zipped.7z is format, My compressed file is going to be saved in path what variable Directory is holding.
The snippet of variables are shown as follow:
Add a ForEach Loop and add a EXECUTE PROCESS TASK inside ForEach Loop container.
Right click on ForEach Loop container and give Edit. Under Collection tab, under Enumerator option, Select ForEach File Enumerator.Now under Enumerator Configuration option, Select Source name where our multiple files are residing.
under files option, keep it as *.* which actually indicates, fetch whatever files are available. (If we give *.txt, It means it is going to fetch all .txt files under any name and if we give say dhina.txt then it is going to fetch only file name dhina.txt)
Now select under Retrieve File Name option, select Name and extension which actually fetches only name and extension of file and sends to any variable we mentioned in variable mappings Tab of ForEach Loop container.
Now under variable mappings Tab of ForEach Loop container, lets select variable User::Filename we created and give ok.
Example for first iteration, Filename will take value txt1.txt and on second iteration, It will take txt2.txt and so on as in Collection Tab, we have selected Name and extension option.
so only name of file and extension of it is passed to variable. if we would selected Name only option in collection tab, then variable FileName would have taken values like txt1, txt2, txt3 on each iteration. (Note: there is no extension this time if we select name only)
Now right click on EXECUTE PROCESS task and go for Edit.
Contrast to filling values in Process Tab, let us make use of Expression tab for passing arguments and values.we know that value we need to fill is,
a "C:\Documents and Settings\\Desktop\test\source\Done.7z" "C:\Documents and Settings\\Desktop\test\source\txt1.txt"
where 'a' is command to start zipping and C:\Documents and Settings\\Desktop\test\source\Done.7z is destination path and file name and other one is source as we mentioned earlier.Since we need multiple files, the command should be altered with the help of variables we created.
since we are using up variables, we can actually Expression tab of Execute Process task.
select property as Arguments and copy paste these following expression. (We can actually analyze by replacing values the variable is possessing)
"a "+ "\"" +@[User::directory] + @[User::zipped]+"\""+" "+ "\""+@[User::directory]+ @[User::filename]+"\""
Then give ok and run the task.
The snippets are as follows:
If we need to insert password for our zipped file, then we can this command:
a "C:\Documents and Settings\\Desktop\test\source\zipped.7z" "C:\Documents and Settings\\Desktop\test\source\txt1.txt" -p"abc where -p represents command for password and abc is password I have given.
If we need to delete the files after archieving, Then we can use following command using WinZip tool as Executable file.
Command: "a " + "\"" +@[User::directory] + @[User::zipped]+"\""+" "+ "\""+@[User::directory]+ @[User::filename]+"\"" + " -ep" + " -df"
For more commands, we can go to command prompt and go to path where 7-Zip is installed and run its .exe file.
my .exe file is in this path and if i run in CMD, then i will get this following results where i can choose any option for further process.
UNZIPPING FILES
In Recent work, I came across a situation to UnZip files.
When I tried to parse arguments
e "<Zipped Source Folder Path>" "<Destination Path For Unzipping Folder>", Error was thrown to check the arguments.
So I quickly changed to by adding "-O" as it is shown above snippet which tells Set output Directory.
So my new Argument was something like this:
e "<Zipped Source Folder Path>" "<Destination Path For Unzipping Folder>" "<-o>".Still the error occurred. Whatever Changes we made in argument placing, the task was failing to Unzip the files.
Then We came across in some other task where Unzip Process was done and we had a look and We came across a Argument "-aoa"
Changes to be made for Unzipping Files:
1) Arguments: e "<Zipped Source Folder Path>" "-aoa"
2) Destination Path For Unzipping Folder: The destination folder where our unzip files should locate can be configured by its value at WORKING DIRECTORY option in Execute Process Task.
Example:
Say I have a Zip File in E:\ Drive and name of Zip file is ExcelToPDF.zip.
I want to unzip this File to Path E:\UnzipFiles\.
So I created Two variables, One for Holding Zipped File Path and Other for Unzipped folder path.
1) @[User::ZippedPath] = E:\ExcelToPDFSite.zip
2) @[User::UnzippedFiles] = E:\UnzipFiles\
So in Execute Process Task, I will change in following way.
In Arguments:
In Working Directory:
And Run the Package. Happy Unzipping!!!
Thank you so much for such a detailed description. It was extremely helpful!
ReplyDeleteNice Work..Greatly helped me Thanks a lot :-)
ReplyDeleteThanks Vinoth. ;-) Bharath is the one who asked me to do this task. :-) :-)
DeleteHi Dinakaran
ReplyDeleteVery good explanation, unfortunately I cannot execute and this package is giving me error. It is creating zip folder for one file but when it process the second file it is giving me an error The process exit code was 2 and the expected was 0. Can you please help me out.
Thanks
Venkat
The problem could be with some variable assignment at execute Process task I guess. Can you zip your package and mail me so that I can debug and fix it and mail you back
DeleteRegards,
Dhinakaran (dhinakaran08@gmail.com)
I have the same problem "exit code was 2". Have you solve this problem?
DeleteRegards,
mail to (ceasor (at) yandex.ru)
In my case it was security issue, 7z has access to zip-file location but no access to temporary-working folder.
Delete-w switch solved the problem
Excellent article. Thanks for your resolution above.
ReplyDeleteThank you so much!!
DeleteVery well explained..Thank you so much...Appreciate your effort
ReplyDeleteThank you so much!!
DeleteAny body use longpath tool, i think this is best tool, please use it
ReplyDelete