Replacing Winzip call in SSIS with 7-Zip?

  • Brandie Tarvin

    SSC Guru

    Points: 172757

    So I have a corporate mandate to remove WinZip from all our servers. We can use 7-zip, but I have no idea how to automate it for use with SSIS packages. I tried PowerShell, but our files are too big for PowerShell's memory. Does anyone have any links or information on how to automate 7-Zip with a command shell line or an Execute Process Task?

    Also, I've been using .lst files with the location and names of the files I was zipping and unzipping. Can 7-Zip be coded to use those or will I have to pass in the file names another way?

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • frederico_fonseca

    SSChampion

    Points: 14685

    you automate it in a similar way to winzip (assuming you were using the command line interface and not the c# dll)

    I'll dig some of my examples and post here

  • Brandie Tarvin

    SSC Guru

    Points: 172757

    The problem is my Google Fu is not finding any way of automating 7-Zip through the Execute Process task. Everything I find requires interaction.

    So your examples would be greatly appreciated. Or links to automating it as well.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • frederico_fonseca

    SSChampion

    Points: 14685

    this is what I do in some (old) packages

    note that the "add to zip " does not delete the files - on newer versions you would need to add "-sdel" to the command line

    you will need to read the 7zip help supplied to understand the parameters - and to use others not mentioned below

    it does allow a file with the list of files to archive/extract - never used it but it is option -ai .. see manual for usage

    from what I remember setting up the correct options was a trial and error as manual at times isn't clear of exactly how some of the options must be defined

    options with -m below

    • -m0=lzma2 - sets compression method to lzma2
    • -mx=3 - sets compression level - 3 is fast compressing
    • -mmt=2 - multithreaded - 2 threads
    Executable from variable/expression
    = fullpath to 7z.exe
    WorkingDirectory - from variable/expression

    to zip
    Arguments - from expression
    "a -t7z -m0=lzma2 -mx=3 -mmt=2 -y " + " \"" + @[User::extract_name] + ".txt.gz\" \"" + @[User::extract_name] + ".txt\""


    to unzip
    "e -o\"" + @[user::extract_file_path] + "\" -y \"" + @[user::extract_name] +".TXT.gz\""
  • rick.thorington

    Valued Member

    Points: 51

    Use the command line executable: https://sevenzip.osdn.jp/chm/cmdline/index.htm

     

  • Brandie Tarvin

    SSC Guru

    Points: 172757

    Do I need to use the .gz extension or can I still use the .zip extension?

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • rick.thorington

    Valued Member

    Points: 51

    You can use gzip. But make sure you use 7z.exe, not 7za.exe -- https://sevenzip.osdn.jp/chm/cmdline/index.htm:

    7za.exe supports only 7z, xz, lzma, cab, zip, gzip, bzip2 and tar formats.

    https://sevenzip.osdn.jp/chm/cmdline/switches/type.htm:

    If -t{archive_type} switch is not specified, 7-Zip uses extension of archive filename to detect the type of archive. If you create new archive, -t{archive_type} switch is not specified and there is no extension of archive, 7-Zip will create .7z archive.

  • frederico_fonseca

    SSChampion

    Points: 14685

    Brandie Tarvin wrote:

    Do I need to use the .gz extension or can I still use the .zip extension?

    you can use the one you desire.

    and you need to change "-t7z" to be the type you wish to use -tZip will create normal zip files

  • Brandie Tarvin

    SSC Guru

    Points: 172757

    Thank you both for your assistance. I'll let you know if I run into any issues.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • Jeffrey Williams

    SSC Guru

    Points: 88531

    Is there a reason why you cannot use the built-in .NET code?

    In a script task, you can include the namespace System.IO.Compression and add a reference to System.IO.Compression.FileSystem - then use the ZipFile function:

    ZipFile.ExtractToDirectory(ZipFile, Directory);

    You can also use that library to create an archive file.

     

     

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Brandie Tarvin

    SSC Guru

    Points: 172757

    HAHAHAHAHAHAHAHAHA. <- This is hysterical laughter, BTW.

    I've just spent a week trying to figure out how to make script tasks do what I want. When I Google for info, I get "zip entire directory" or "zip one file".

    I know next to nothing about that code. Trying to find a article describing the "Zip specific files with different names in a directory that can have dozens and dozens of different files and make sure it doesn't fail if one of those files is missing" is beyond my Google-Fu.

    Plus if I can use 7-Zip, I just have to update config files without replacing my Execute Process steps in all the SSIS packages I'm updating. Otherwise I'm editing a dozen or more SSIS packages to change tasks and then I have to put them through our full SDLC process because I changed the packages and … well, stuff.

    If I have to chase the .Net thing, I will. But it's hard concentrating on code I don't know when working from home with a toddler that wants constant attention. I have to start over reading everything from scratch every time I get interrupted. So I decided to start over with 7-Zip because I know the GUI interface at least and figured the command lines couldn't be too different from WinZip assuming that it could be automated.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • Jeffrey Williams

    SSC Guru

    Points: 88531

    I get all that - but changing over to 7-zip is going to require modifying the Execute Process Task to call a different executable with different command line parameters.

    Here is the first item I found searching for .NET zipfile: http://itpachie.blogspot.com/2015/05/ssis-extract-zip-file-using.html - looks like it is fairly well documented.  I switched my packages over to using a script task to unzip files - and I find it to be much easier to manage, especially since I don't have to worry about installing anything on the server.

    The documentation here (https://docs.microsoft.com/en-us/dotnet/api/system.io.compression.zipfile?view=netframework-4.8) has example code that can be incorporated into the SSIS script task fairly easily to fit any requirement.

    And once you have done it one time...it is easy to copy/paste the code.

    Or...as I did - I created a separate package that just performs the extract and include that in the project where needed.  Setup the parameters and any zip files in a defined directory will be unzipped and copied/moved to a destination.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Brandie Tarvin

    SSC Guru

    Points: 172757

    Jeffrey Williams wrote:

    I get all that - but changing over to 7-zip is going to require modifying the Execute Process Task to call a different executable with different command line parameters.

    No. It will require me to alter the config file. Because that's where I store the command line, the arguments, etc. If I do this right, I won't actually have to touch the SSIS package at all.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Viewing 13 posts - 1 through 13 (of 13 total)

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