I want to zip and delete my sourcefiles within the SSIS package when I have processed them.
Solution
This is a follow up on the unzip article. There are a lot of options to zip files within SSIS. For example:
- Third party, open source and my own ziptasks
- Execute Process Task in combination with Winzip commandline
- Script task with opensource zip dll
- Script task with System.IO.Compression (works only for gzip)
- Script task with Visual J# Redistributable Packages (works only for zip)
This solution uses the Microsoft Visual J# Redistributable Packages (don't worry you don't have to write J#), because it's free and you don't have use a third party or opensource dll on a production server which is often a big deal.
1) Visual J# Redistributable Packages
Install Microsoft Visual J# Redistributable Packages on your machine(s). You can download the correct version (x84, x64, IA64) at Microsoft.
2) Variables
Create two String variables named SourceFile and ArchiveFolder to store the filename and the zip/archive folder. Fill the ArchiveFolder with the folderpath where you want to store the zip files (for example H:\SoureFiles\Archive\).
Two String variables |
3) Loop through folder with sourcefiles
This example loops through a folder, processes all files and then archives them. Create a foreach loop that loops through a folder with some text files (content doesn't matter for this example). Add an empty dataflow just for decoration. You can use that one later to process the textfiles.
Foreach file enumerator |
4) Map variable
Map the SourceFile variable to index 0 in the Foreach Loop.
Variable Mappings |
5) Script task with variables
Create a Script task within the Foreach loop and select the two variables as ReadOnly.
ReadOnly Variables |
6) Zip with Script task
Add a reference to the Microsoft Visual J# Redistributable Packages dll in your script. The name of the .NET dll is vjslib. After you have added it, it shows on the right site (3).
In Project menu, Add Reference... |
After that add the following code (method and usings)
// C# code using System; using System.Data; using Microsoft.SqlServer.Dts.Runtime; using System.Windows.Forms; using System.IO; // Added using java.util.zip; // Added using java.io; // Added namespace ST_967ebb1a53884e89ac28f0a69ad082f1.csproj { [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")] public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase { #region VSTA generated code enum ScriptResults { Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success, Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure }; #endregion public void Main() { // Variables for zipfile and unzip folder String SourceFile; SourceFile = Dts.Variables["SourceFile"].Value.ToString(); String ArchiveFolder; ArchiveFolder = Dts.Variables["ArchiveFolder"].Value.ToString(); // Determine zipfile path: archivefolder + filename (without path and extention) + "zip" // Example: // SourceFile = c:\temp\test.txt // ArchiveFolder = c:\temp\archive\ // Then ZipFile = c:\temp\archive\test.zip String ZipFile; ZipFile = ArchiveFolder + SourceFile.Substring((SourceFile.LastIndexOf(@"\") + 1),(SourceFile.LastIndexOf(".") - SourceFile.LastIndexOf(@"\"))) + "zip"; // Create Zipfile FileOutputStream fos = new FileOutputStream(ZipFile); ZipOutputStream zos = new ZipOutputStream(fos); // Add zipfile FileInputStream fis = new FileInputStream(SourceFile); // Remove drive and replace '\' with '/' for internal name // Example: // SourceFile = c:\temp\test.txt // Then internal name = temp/test.txt ZipEntry ze = new ZipEntry(SourceFile.Substring(3).Replace('\\', '/')); zos.putNextEntry(ze); // Loop through sourcefile to zip it sbyte[] buffer = new sbyte[1024]; int len; while((len = fis.read(buffer)) >= 0) { zos.write(buffer, 0, len); } // Close all objects zos.closeEntry(); fis.close(); zos.close(); fos.close(); // Finally delete the source file System.IO.File.Delete(SourceFile); Dts.TaskResult = (int)ScriptResults.Success; } } }Note: you can delete the sourcefile with a File System Task aswell, but this is only one row of code extra.
7) Test
The end result looks something like this. Take it for a test drive...
The end result |
Note: This is a very basic script. You can add tons of checks and other features to make sure the script always works. Let me known if you have any questions. A unzip example is available here.
how to do Execute Proces Task in combination with Winzip commandline?
ReplyDelete@chatura: You have to create an expression for the Argument property of the Execute Proces Task.
ReplyDeleteSomething like:
"-a -ex " + @[user::myZipfile] + " " + @[user::mySourcefile]
Hi Joost,
ReplyDeleteI am considering to implement an SSIS compression using 'System.IO.Compression'. Do you know of a good example for that?
Regards,
Igal
Hi Igal,
ReplyDeleteYou should have a look at the MSDN SSIS Forum. There are some examples like this one: System.IO.Compression
Hi Joost,
ReplyDeleteI have to zip few text files(ex: 1.txt , 2.txt ,3.txt etc)
into 1.zip ,2.zip ,3.zip in different folder.
I am doing in execute process task and for zipping I am using 7-Zip.
can you please help me.
Ankur
@Ankur: Use the Execute Proces Task instead of the Script Task. For the parameters you have create an expression for the Argument property of the Execute Proces Task
ReplyDeleteSomething like:
"a " + @[user::myZipfile] + " " + @[user::mySourcefile]
Thanks for helping me Joost.
ReplyDeleteI have 1 more question.I have a folder in which I have 4 text files. I have to move them in separate folders.
ex: one folder has (1.txt,2.txt,3.txt,4.txt). i have to create folder1(1.txt), folder2(2.txt), folder3(3.txt), folder4(4.txt)
Is this possible.
Please if you know something ,let me know.
Ankur
@Ankur: See msdn forum
ReplyDeleteHi,
ReplyDeleteHow would you change this so that it creates one zipped file for all contents of the source folder?
@Cathal O'Brien: instead of passing the filepath of the source file to the script, you should pass the folderpath.
ReplyDeleteThen add a loop at row 39, that loops through all files in that folder (see script example here).
The rows 40 to 69 should be (slightly altered for the path retrieving) moved within the loop.
Let me know if you get it working...
In your Step#6 I am unable to see vjslib in project reference window. the list of component it shows me are filterd for .Net 4.0. I don't know how to unfilter them. thoughts?
ReplyDelete@Anonymous:
ReplyDeleteTry changing the Target Framework of your Script Task: when you edit, go to the Project Explorer and right click the projectname (some kind of guid). Select Properties and check the application tab.
Let me know if it worked and if not tell me:
- Operating system
- Your SSIS version
- Are you using C# or VB.net?
- Which version of the Visual J# Redistributable Packages did you install?
Hello,
ReplyDeleteIf instead of reading from a File I want to read from a string variable and write it to the zipFile directly?
Any suggestion?
Thank's for your time
The easiest would be to first write the variable value to a textfile and then zip the textfile. That could be done with only a few lines of code: http://msdn.microsoft.com/en-us/library/8bh11f1k.aspx
Delete