Saturday 8 January 2011

Zip sourcefile to archive

Case
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:
* Update: my own zip task *





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.

14 comments:

  1. how to do Execute Proces Task in combination with Winzip commandline?

    ReplyDelete
  2. @chatura: You have to create an expression for the Argument property of the Execute Proces Task.

    Something like:
    "-a -ex " + @[user::myZipfile] + " " + @[user::mySourcefile]

    ReplyDelete
  3. Hi Joost,
    I am considering to implement an SSIS compression using 'System.IO.Compression'. Do you know of a good example for that?

    Regards,
    Igal

    ReplyDelete
  4. Hi Igal,

    You should have a look at the MSDN SSIS Forum. There are some examples like this one: System.IO.Compression

    ReplyDelete
  5. Hi Joost,

    I 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

    ReplyDelete
  6. @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

    Something like:
    "a " + @[user::myZipfile] + " " + @[user::mySourcefile]

    ReplyDelete
  7. Thanks for helping me Joost.

    I 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

    ReplyDelete
  8. Hi,

    How would you change this so that it creates one zipped file for all contents of the source folder?

    ReplyDelete
  9. @Cathal O'Brien: instead of passing the filepath of the source file to the script, you should pass the folderpath.

    Then 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...

    ReplyDelete
  10. 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
  11. @Anonymous:
    Try 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?

    ReplyDelete
  12. Hello,

    If 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

    ReplyDelete
    Replies
    1. 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

Please use the SSIS MSDN forum for general SSIS questions that are not about this post. I'm a regular reader of that forum and will gladly answer those questions over there.

All comments are moderated manually to prevent spam.

Related Posts Plugin for WordPress, Blogger...