Post reply

Using SSIS to zip files and email the zipped files

  • September 23, 2008 at 3:23 am

    #875507

    This is really a great article with great simplicity.

    Thanks for sharing.

    Please keep it up

  • September 23, 2008 at 3:43 am

    #875515

    Hi,

    Nice article.

    I rather instead of using an Execute Process Task , that uses an external file, it's better (for me) to use the 7z instruction on the Script Task and put the 7z.exe in the Windows Assembly folder.

    Regards.

  • September 23, 2008 at 5:03 am

    #875548

    Hi,

    I agree whit this article, but when I develope this kind of package (Mail Task, FTP Task, Execute Process task), I prefere use the expressions andthe variables, evaluated as Expressions. I try to use the Script Task only for some task that SSIS can not do natively.

    Sorry for my english 🙂

    Rmi

  • September 23, 2008 at 3:45 pm

    #876052

    Bk (9/22/2008)

    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

    No, you cannot do this via Windows Compressed folders. You cannot do this because Windows Compressed folders does not have an interface or allow the use of command line switches . You could probably do some hacks to get it working, but it is outside the Windows built-in functionality. You will have to go with a non-Windows tool or API to accomplish this task.


    My blog: http://jahaines.blogspot.com

  • September 23, 2008 at 6:29 pm

    #876071

    NO Windows API to manipulate compressed folders? Ok then....

  • September 26, 2008 at 4:15 pm

    #878005

    There are compression functions that are part of the .NET framework that you can use inside of a Script task. This is the method I have previous chosen and it works great. A colleague on my team chose to go with the 7-zip route to decompress for a package he wrote, which works great as well. One difference though is that for an unknown reason the daily 7-zip process fails around once a month and sits on the server hung until someone force-quits the process. The SSIS job doesn't error out until after 7-zip is force-quit. The 7-zip execution doesn't report errors or kill the package execution, whereas if the .NET Script task fails it would throw an error inside of SSIS. I'm not sure if the hung script is related to how the other developer coded the package, but it is something to watch out for.

  • October 8, 2008 at 2:17 pm

    #882561

    This is excellent. Is there any way to take it a step further and input the password into the 7-zip file, and set any of the 7-zip file options?

    Thanks

  • October 9, 2008 at 2:09 am

    #882734

    This is excellent. Is there any way to take it a step further and input the password into the 7-zip file, and set any of the 7-zip file options?

    If you are using the execute process task, just put any of the options in the arguments section.

    If using a script task and using the System.Diagnostic.Process classes, use the StartInfo property to set application name and the arguments. see http://msdn.microsoft.com/en-us/library/system.diagnostics.process.startinfo(VS.80).aspx

  • October 9, 2008 at 3:21 pm

    #883143

    Hi Carolyn

    An excellent and simple example though I have a question.

    What this does not do seem to do which would be very handy is zip multiple sub-directories that contain files. Is there an argument that needs to be passed to the zip program?

    Thanks in advance

    Mark

  • October 9, 2008 at 7:32 pm

    #883198

    The 7zip documentation is very good and it will answer your question. You can do pretty much whatever you can think of doing with a compression program by sending in the correct command-line arguments. The manual for 7zip comes with the installer.

  • October 11, 2008 at 8:40 am

    #883769

    piet_dj

    M Chabot is correct here the instructions you download with 7 zip will give you the syntax to password protect the file if this is needed.

    I find it useful to use a message box (msgbox) in the script task to ensure that I am creating an accurate variable to use as the command string in SSIS as it can be a bit fiddly. Whilst you set it up.

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

  • October 13, 2008 at 5:02 pm

    #884362

    Hi thx - I managed to work it out myself by trial and error - works great. My next task is try and do it with WINZIP (users want to see a compression file type that they are used to!!) and I am having less success in doing the same. If anyone could let me know the syntax for passing the argument/s to the zip app for zipping a foler with multiple sub-folders and files - that would be great.

  • October 13, 2008 at 5:33 pm

    #884368

    You might be overlooking the fact that 7-zip can create .zip files in addition to .7z files.

  • October 13, 2008 at 5:37 pm

    #884369

    I would add that I'm positive that 7-zip can do recursive compression. I have done this many times on the command line. The argument for recursion I believe is "-r"

  • November 10, 2008 at 9:58 am

    #896394

    I am new to SSIS and VB. The article was very usefull. But I was unable to run the package using WINRAR.

    Will this work for WINRAR (may be I need to change the arguments).

    Any help would be greatful.

Viewing 15 posts - 16 through 30 (of 81 total)

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