Using SSIS to zip files and email the zipped files

  • Carolyn Richardson

    SSCrazy Eights

    Points: 8352

    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]

  • Anipaul

    SSC-Insane

    Points: 24681

    Nice article ....

  • shrikant-279400

    SSCommitted

    Points: 1651

    Hi Carolyn,

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

    Regards
    Shrikant Kulkarni

  • 0x30

    SSC Veteran

    Points: 299

    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.

  • Idris Ogueboule

    SSC Enthusiast

    Points: 129

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

  • Carolyn Richardson

    SSCrazy Eights

    Points: 8352

    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]

  • Bk-384756

    SSC Veteran

    Points: 221

    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

  • Festeron

    SSC Eights!

    Points: 927

    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

  • Liam Gavin-382521

    SSC Veteran

    Points: 239

    Great article, thanks for sharing

  • Carolyn Richardson

    SSCrazy Eights

    Points: 8352

    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]

  • Dan Guzman - Not the MVP

    Hall of Fame

    Points: 3755

    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

  • Bk-384756

    SSC Veteran

    Points: 221

    (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?

  • TimothyAWiseman

    SSCrazy Eights

    Points: 8819

    Excellent article. Thank you.

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

  • 0x30

    SSC Veteran

    Points: 299

    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 🙂

  • bc_

    Hall of Fame

    Points: 3106

    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