We help IT Professionals succeed at work.

How to unzip the compressed files into the local folder and then read it from there usign sql server ssis

srionline2k6
srionline2k6 asked
on
Medium Priority
2,663 Views
Last Modified: 2013-11-10

I am an ssis developer and I need to create an ssis package where I have the CSV files which are compressed in .gz files located at located at http://LabSql01/oxford/mst/import/ .  The files are named  with the convention of <table name>.csv.gz  How can i use the script task to get the files unzipped into the local folder and then get the files from the unzipped folder to read through the file.
There is only one file that exists in the unzipped folder.

Please guide me through how can i go about it ?
Comment
Watch Question

CERTIFIED EXPERT

Commented:
you could use the "Execute Process Task" .
This will allow you to run an unzip program.
It must be an unzip program that takes command line parameters. Most do.

You have to remember that the SSIS program must be able to see all the required folders, including where you have stored the unzip program.
Commented:
you can download the zip file using the below process

http://www.sqlis.com/post/Downloading-a-file-over-HTTP-the-SSIS-way.aspx

Unzipping the file can be done using execute process task
http://blog.cybner.com.au/2008/05/unzip-files-using-ssis.html
http://www.ssisguru.com/2009/10/unzip-and-zip-files-using-ssis.html

And you can use a For Each Loop to loop through the Files and load them into the tables

http://www.sqlis.com/post/Looping-over-files-with-the-Foreach-Loop.aspx
Alpesh PatelSolution Architect at TCS (SAFe®Agilist, TOGAF® Foundation, PSM I®, MCP, MCSD)
CERTIFIED EXPERT
Commented:
follow the image below: image
Jason YousefSr. BI  Developer
Commented:
You have 6 options, This article will give you all the options..
http://microsoft-ssis.blogspot.com/2011/01/unzip-files-with-ssis.html

Personally speaking, I use SSIS Task UnzIp  it's free, as we don't install any software on production server such as winzip and these applications.