Pages

October 14, 2008

SSIS: Compress Files Using Execute Process Task

From time to time people ask how you could compress or extract compressed files in SSIS. While SSIS does not have any built-in taks to do that, you can certainly use execute process task to run a command line using the utility file archiver software of your choice. In the example below, I am using a 7-zip command line to compress a file. 7-zip is open source software (yes, it is free!); but you could use any other tool that provides support for command line.


In this example, a file c:\temp\test.xlsx is added to a file c:\temp\test.zip


Notice that the excutable path/file provided in Executable property must exist in any server runing the package (no just in your development environment). Also, remember that most properties of the task can be affected via expressions, that would come handy for changing the path and or file names at run time.

The argument property must be changed to match the proper syntax, depending on your tool of choice.
Posted by

22 comments:

  1. Unknown3/11/08 2:41 PM

    Loop thru and execute a process task?

    ReplyDelete
  2. Unknown3/11/08 2:42 PM

    How can I loop thru and execute a process task with 7z

    ReplyDelete
  3. Unknown4/11/08 1:08 PM

    This comment has been removed by the author.

    ReplyDelete
  4. Unknown8/11/08 7:20 AM

    Never mind i figured it out. Thanks.

    ReplyDelete
  5. Unknown10/6/09 3:08 PM

    Thanks for the simple explanation – since I’m new to all this, it’s exactly what I needed.
    However, I’m wondering if you have any idea why I cannot email my .zip file using sp_send_dbmail, from an Executie SQL Task? The original file (.xls) emails our just fine, but when I zip the file, I get the following:
    [Execute SQL Task] Error: Executing the query "EXEC [msdb].[dbo].[sp_send_dbmail] @profile_name ..." failed with the following error: "Failed to open attachment file 'C:\SQLServer2008…\TheFile.zip'. Access denied.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
    I know your post is about zipping a file, but just wondered if you had any suggestions. Thanks!!

    ReplyDelete
  6. Rafa Salas27/6/09 10:47 PM

    ctaylor,
    I haven't used sp_send_dbmail. Perhaps you should post that in the SQL Server forums

    ReplyDelete
  7. Slick18/3/10 5:23 PM

    Hi Rafael,

    I am doing exactly same but I keep getting an error.

    The only difference what I'm doing is I am unzipping a gz file.

    In the executable I have defined the path to my 7z.exe file and in the argument I have the following:

    7z e filename_reviews_*.xml.gz -oc:\FolderName\Vendor

    What am I doing wrong? I tried without the 7z, with the 7z. Also, i tried with the executable 7za.exe. nothing works!

    Please help.

    Thanks,
    S

    ReplyDelete
  8. [m@rtin]15/4/10 12:01 PM

    Thannkxx man

    ReplyDelete
  9. Rafa Salas15/6/10 8:45 AM

    Slick,
    Does the resulting command line from the expression work outside of SSIS? I would double check that the 7zip command is valid in a command line prompt.

    ReplyDelete
  10. Unknown9/7/10 3:39 AM

    Hi Rafael,
    In my execute process task,I used sqlcmd.exe to export tables and it runs fine when it is run from "execute package utility" or visual studio but fails when I run it through SQL agent job showing error at working directory of the "execute process task". What could be the issue?
    Vivek

    ReplyDelete
  11. Rafa Salas9/7/10 8:10 AM

    Vivek,
    There may be a number of reasons that would explain your issue. Most of the time this type of problem is related to permissions. Something to be aware is that the SS agent jobs run under the account running the SS agent service, so check the permissions on that account or create a proxy account.

    ReplyDelete
  12. Unknown12/7/10 6:25 AM

    Thanks Rafeal, its working now ;).
    I read your many post about SSIS and all of them are simple and useful.

    ReplyDelete
  13. Rafa Salas12/7/10 9:46 AM

    Vivek,
    I am glad it is working now. Thanks a lot for reading my stuff!

    ReplyDelete
  14. Saluki17/8/10 10:56 AM

    Hi Rafael,

    I was wondering if you can use variables in the arguments field. What I'm trying is something like : wzunzip -o- -ybc @[SourceFileName] @[DestinationPath]

    This field doesn't seem to understand variable replacements.

    Thanks

    ReplyDelete
  15. Anonymous30/8/10 5:08 PM

    Hi Rafael,
    Can you tell me how can i set FailTaskIfReturnCodeIsNotSucessValue to False, If I am creating this package and task programmatically using C# ?

    ReplyDelete
  16. Unknown29/9/10 4:23 AM

    Hi Sulaki,
    It will work, you have to use expression for the argument and initialize the variable with any value.
    Thanks
    Vivek

    ReplyDelete
  17. Anonymous21/12/10 8:26 AM

    Hi Rafael ,

    Can you please post the ARGUMENT thati can use to zip the file with password protection

    ReplyDelete
  18. Anonymous21/12/10 8:27 AM

    Hi Rafael ,

    Can you please post the ARGUMENT thati can use to zip the file with password protection in SSIS

    ReplyDelete
  19. Anonymous29/4/11 4:58 PM

    Hi Rafael ,

    When i schedule my job which zips and uploads the file, the job runs successfully,but the file is not zipped. Can you please help on this

    ReplyDelete
  20. anonimo ssis 20081/9/12 10:57 PM

    Hi Rafael, Very good article,
    A query like that could do to a "script task" to call a Visual Basic control is a control net "NotifyIcon", instead of using a msgbox using a "NotifyIcon" if I pass an error produciera SSIS variable 2008.

    ReplyDelete
  21. aaaa15/5/13 11:49 AM

    Hi Rafael ,

    I am trying to Zip some XML,XLS files through 7-zip.exe ,generated from ssrs report.
    So when there are more number of files generated from report, my package fails ti zip those.I think command line arguements exceeds the maximum length.

    So how to handle this scenario.

    Thanks
    Alok

    ReplyDelete
  22. Unknown19/3/14 4:17 PM

    This comment has been removed by the author.

    ReplyDelete

I will love to hear your feedback and comments. Thanks.

Subscribe to: Post Comments (Atom)