DTS Problem Unziping Files

[AnaFlor]

Hello,

I have a source file that is zipped.
I want to create a DTS that unzip the file and then use that file to load data into the database.

How can I do this?

Thanks

 

[JayKusch]

Pretty easy to do BUT you will need to buy a licensed copy of WinZip ... and as often as EVERYONE uses it ... its worth buying a copy just once - LOL.

You will also have to download the current version of the WinZip Command Line interface from their site.

The reason for the license is that when you register Winzip, it removes the commercial/company header message from being displayed when you run Winzip in the command line. If you do not register the software, the Unzip will hang. So in say that ... here is the code to unzip a file thru SQL Server ...

declare @ZipFileName VarChar(50)
declare @SQLCommand  VarChar(400)

SET @ZipFileName = 'MyZipFile.Zip'

SET @SQLCommand = 
'exec master..xp_cmdshell ' + '''' + 'C:\MyDir\Winzip\WZUNZIP -yb  C:\MyDir\' 
+ @ZipFileName + ' C:\MyDir\' + ''''

EXEC (@SQLCommand)

What the code is doing above is starting WZUNZIP that has been installed in the C:\MyDir folder. It is Unzipping a zipped file called MyZipFile.zip which was passed in as a variable. It will unzip the file to the C:\MyDir folder.

This should get you most of the way! Enjoy!


Thanks

J. Kusch

 

[AnaFlor]

What if my ziped file and the file inside it is dynamic.
Imagine that I receive one file for each day and the name is exampleyyyymmdd?

How can I solve that?

Thanks

 

[JayKusch]

declare @ZipFileName VarChar(50)
declare @SQLCommand VarChar(400)

SET
@ZipFileName = 'MyZipFile.' + CONVERT(Char,GetDate(),112)

SET @SQLCommand =
'exec master..xp_cmdshell ' + '''' + 'C:\MyDir\Winzip\WZUNZIP -yb C:\MyDir\'
+ @ZipFileName + ' C:\MyDir\' + ''''

EXEC (@SQLCommand)



Thanks

J. Kusch

 

[AnaFlor]

Thanks for all your help JayKusch.

 

[123ASP]

Hi,

I am interested to learn command line zip and unzip . Where do I get the program where it help me run the zip / unzip command line.
thanks
Al

 

[JayKusch]

Purchase WinZip at

http://www.winzip.com/

Then pick up the command line interface from ...

http://www.winzip.com/prodpagecl.htm

Thanks

J. Kusch