Using SSIS to zip files and email the zipped files

  • Comments posted to this topic are about the item Using SSIS to zip files and email the zipped files

    Facts are stubborn things, but statistics are more pliable - Mark Twain
    Carolyn
    SQLServerSpecialists[/url]

  • Nice article ....

  • Hi Carolyn,

    you have made it really simple. awesome!!! [:)]

    Regards
    Shrikant Kulkarni

  • Or instead of breaking out into a external process, you can use the script task and SharpZipLib (http://www.icsharpcode.net/OpenSource/SharpZipLib/) or DotNetZip (http://www.codeplex.com/DotNetZip) C# Zip frameworks.

    This is how we utilize zipping requirements in our control flows.

  • Good article Carolyn. Any idea about the maximum size of the files that can be zipped or the zipped file itself?

  • I think its up to 4GB, using 7 zip, but you should really check on the 7 zip site http://www.7-zip.org/. If the files you want to zip are bigger than this, you could always look at the products Grasshopper mentions.

    Facts are stubborn things, but statistics are more pliable - Mark Twain
    Carolyn
    SQLServerSpecialists[/url]

  • Thanks for the great article, Carolyn!

    Shouldn't it also be possible to use native Windows functions to compress and uncompress groups of files into .zip-compatible archives? NOT "compress" like the command line .exe or operating system file attribute, but similar to how I can view/create compressed .zip-compatible files from Windows Explorer.

    Any thoughts?

    Bk

  • It's nice to see zipped files being used by SSIS, and most of the article was good, but surely there's a bug in this code:

    If Len(strDay) = 1 Then

    strMonth = "0" + strDay

    End If

  • Great article, thanks for sharing

  • Shouldn't it also be possible to use native Windows functions to compress and uncompress groups of files into .zip-compatible archives? NOT "compress" like the command line .exe or operating system file attribute, but similar to how I can view/create compressed .zip-compatible files from Windows Explorer.

    Bk

    I'm sure it's entirely possible to use native Windows functions to compress and uncompress groups of files into .zip-compatible archives. The article justs shows one method there are probably many ways to do the same thing. When I was tasked with zipping files, I could find no real world examples so had to figure it out myself, as its something thats likely to be needed again I thought I would document the way I chose to do it.

    Festeron

    Oops well spotted I will change later...to

    If Len(strDay) = 1 Then

    strDay = "0" + strDay

    End If

    Facts are stubborn things, but statistics are more pliable - Mark Twain
    Carolyn
    SQLServerSpecialists[/url]

  • I saw that too.

    If Len(strDay) = 1 Then

    strMonth = "0" + strDay

    End If

    probably should be

    If Len(strDay) = 1 Then

    strDay = "0" + strDay

    End If

  • (grin) Maybe I should have been less obtuse...

    What I was REALLY hoping for was "someone" to share how they'd done it WITH the Windows functions.

    "OH GREAT SOMEONE, OUT THERE IN THE INTERWEB... Hear my request..."

    Does the Interweb respond to sacrificial offerings?

  • Excellent article. Thank you.

    ---
    Timothy A Wiseman
    SQL Blog: http://timothyawiseman.wordpress.com/

  • What I was REALLY hoping for was "someone" to share how they'd done it WITH the Windows functions.

    Well maybe this example is not with windows functions as they are API calls. But I can give an example using a .NET DLL Assembly!

    1) Download SharpZipLib and install into the GAC (yes, has to be in the GAC. This is a MS decision!)

    2) Add a script task

    3) Edit the script

    4) Add a reference to the DLL by right clicking on references and adding the reference

    5) Initialise the object and use the assembly functions for Zip creation 🙂

    We use package variables for the output dir and the name of the zip file (we add date info to this filename).

    If you want, I possibly could write up a step-by-step article on how to achieve this in managed code.

    Hope this makes sense 🙂

  • there is a free component that does this for you:

    http://www.thejoyofcode.com/SSIS_Compress_File_Task.aspx

    also, you can use an Execute Process Task and call winzip.exe:

    http://dichotic.wordpress.com/2007/07/24/using-winzip-within-ssis/

    [font="Arial Narrow"]bc[/font]

Viewing 15 posts - 1 through 15 (of 82 total)

You must be logged in to reply to this topic. Login to reply