none
Execute Process Task Unzipping .gz files monthly

    Question

  • I have a monthly .txt.gz file dump thats zipped in .gz format. Each month the name changes to reflect the month and year. I've been trying to build a process to unzip the file and import the txt into a SQL table.  Is there a way to automate the entire process? I currently use 7zip monthly to unzip and manually import, however I'm fairly certain SISS should be able to handle it.
    Tuesday, July 19, 2011 10:19 PM

Answers

  • Yes, it can.

    You can use a Foreach Loop Container to loop over the (one) file you happen to have in a certain folder.  Inside the loop, that'll place the filename into an SSIS variable.  You can then use an Execute Process Task to issue your 7zip command to extract the contents, and then process the contents...


    Todd McDermid's Blog Talk to me now on
    • Marked as answer by Eileen Zhao Monday, July 25, 2011 2:40 PM
    Tuesday, July 19, 2011 11:45 PM
    Moderator
  • example of decompressing gzip file here: http://msdn.microsoft.com/en-us/library/system.io.compression.gzipstream.aspx

    To get the file name you can use either code or SSIS expressions based on either current date or (my preferred approach) log the last file that was used in a metadata table in your db - use that to determine what the next file should be named


    Rgds Geoff
    • Marked as answer by Eileen Zhao Monday, July 25, 2011 2:40 PM
    Wednesday, July 20, 2011 3:51 AM

All replies

  • Yes, it can.

    You can use a Foreach Loop Container to loop over the (one) file you happen to have in a certain folder.  Inside the loop, that'll place the filename into an SSIS variable.  You can then use an Execute Process Task to issue your 7zip command to extract the contents, and then process the contents...


    Todd McDermid's Blog Talk to me now on
    • Marked as answer by Eileen Zhao Monday, July 25, 2011 2:40 PM
    Tuesday, July 19, 2011 11:45 PM
    Moderator
  • example of decompressing gzip file here: http://msdn.microsoft.com/en-us/library/system.io.compression.gzipstream.aspx

    To get the file name you can use either code or SSIS expressions based on either current date or (my preferred approach) log the last file that was used in a metadata table in your db - use that to determine what the next file should be named


    Rgds Geoff
    • Marked as answer by Eileen Zhao Monday, July 25, 2011 2:40 PM
    Wednesday, July 20, 2011 3:51 AM