unzip files using DTS

Question

unzip files using DTS

Sridhar-137443

Hall of Fame

Points: 3762
More actions
April 12, 2005 at 2:32 pm

#60009

Hi,
I need to create a DTS package which will first unzip the files and put into specific directory and then load the data. Please let me know if you have any idea how to create the dts package and which tools I need to use. The zip files have the extension .gz. like 20030410.csv.gz
Thanks,
Sridhar.

Viewing 9 posts - 1 through 8 (of 8 total)

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

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 1

You will need to have a registered unzip i.e. PKZIP, WinZip, etc... You will also need a command-line editor (separate piece for WinZip). You will then need to write VBScript to unzip, push the file around, etc... prior to continuing the DTS package

Good Hunting!

AJ Ahrens

webmaster@kritter.net

Micha Petrovic-156323 Newbie Points: 7 More actions · Answer 2

Hi there,

there is a command line utility named gzip.exe and is very simple to use.

Micha

Aaron West SSC Eights! Points: 808 More actions · Answer 3

Why not just use Perl, DBI and DBD :: ODBC? I hate DTS.

use strict;

#use DBI;

#use DBI qw(:sql_types);

use Compress::Zlib;

open FH, "<file.gz";

binmode FH;

{ local $/ = undef; my $filetext = <FH>}

$filetext = Compress::Zlib::memGunzip( $filetext );

# now either parse your file and insert records

# using a statement handle (see perldoc DBI)

# or just write the file out and call bcp

open FH, ">file.txt";

print FH $filetext;

close FH;

print `bcp ...`;

Seriously, I'm sure you can get it to work in DTS. I bet there are some activeX controls for ZIP you could embed into a DTS script. In any case, a web search might find a solution, such as this use of the WScript.Shell object:

http://www.sqltalk.org/ftopic17451.html

alkrup-104967 SSC Enthusiast Points: 147 More actions · Answer 4

Hi people

AJ Ahrens right and u can add to DTS - Execute Process Task without using VBScript...

swede SSCommitted Points: 1773 More actions · Answer 5

its not necessary to write an vbscript or a peril program, just use the store procedure xp_cmdshell to execute your dos command

Roy Ashbrook SSChasing Mays Points: 632 More actions · Answer 6

gzip is free.

exec master.dbo.xp_cmdshell 'gzip -d ... file'

exec master.dbo.xp_cmdshell 'bcp ... file'

sergioman SSC Enthusiast Points: 166 More actions · Answer 7

Hi hope this helps.

'**********************************************************************

' Visual Basic ActiveX Script

'************************************************************************

Function Main()

'

' Create needed FileSystemObject and setup the directories for the file locations.

'

Set objFSO = CreateObject("Scripting.FileSystemObject")

strLogFile = "\\servername\drive_letter$\directory\Logs\unzipdata.log"

strZipArchiveDir = "\\servername\drive_letter$\directory\data"

strDir = "\\servername\drive_letter$\directory"

'-----------------------------------------------------------------------------------------------------------

'check to see if log files exists, if not create it

'-----------------------------------------------------------------------------------------------------------

If not objFSO.FileExists(strLogFile) then

Set master = objfso.CreateTextFile(strLogFile)

master.WriteLine " ***********************************************************"

master.WriteLine " File Created @ " & now

master.WriteLine " In LAST File Exists Routine @ " & now

master.WriteLine " ***********************************************************"

master.WriteLine

Else

Set master = objFSO.OpenTextFile(strLogFile,8)

master.WriteLine

master.WriteLine " ***********************************************************"

master.WriteLine " File Opened @ " & now

master.WriteLine " In LAST File Exists Routine @ " & now

master.WriteLine " ***********************************************************"

master.WriteLine

End if

yr=year(date)

mo=month(date)

if mo<10 then

mo = "0"&cstr(mo)

end if

dy=day(date)

if dy < 10 then

dy = "0" & cstr(dy)

end if

'-----------------------------------------------------------------------------------------------------------

'Look to see if a file matches the backup db with todays date

'-----------------------------------------------------------------------------------------------------------

Set objFSOFolder = objfso.getfolder(strZipArchiveDir)

for each objFSOFile in objfsofolder.files

if ucase(right(objFSOFile.name,3)) = "BAK" then

strFileNameBase = trim(left(objFSOFile.Name, len(objFSOFile.name)-4))

if trim(left(strFileNameBase,len(strFileNameBase)-4)) = "main" then

'-----------------------------------------------------------------------------------------------------------

'delete a the bak file

'-----------------------------------------------------------------------------------------------------------

'msgbox objFSOFile.name

objfso.DeleteFile strZipArchiveDir &"\"& objFSOFile.name

strFileNameBase = "main_"&yr&mo&dy

master.WriteLine objFSOFile.name & " ***********************************************************"

master.WriteLine objFSOFile.name & " DETECTED AND DELETED @ " & now

master.WriteLine objFSOFile.name & " ***********************************************************"

exit for

end if

strFileNameBase = "main_"&yr&mo&dy

'----------------------------------------------------------------------------------------------------------

' check to see if the zip file exists for today

'----------------------------------------------------------------------------------------------------------

If fso.FileExists(strZipArchiveDir & "\" & strFileNameBase & ".zip") then

master.WriteLine strFileNameBase & " *************************************************************************"

master.WriteLine strFileNameBase & " .zip exists @" & now

master.WriteLine strFileNameBase & " ZIP FILE EXISTS - SUCCESS "

master.WriteLine strFileNameBase & " **************************************************************************"

master.WriteLine

'-----------------------------------------------------------------------------------------------------------

'unzip file of the backup file

'-----------------------------------------------------------------------------------------------------------

Set WshShell = CreateObject("WScript.Shell")

strzip = "wzunzip -o " & strZipArchiveDir & "\" & "main_" & yr&mo&dy & ".zip " & strZipArchiveDir &"\"

WshShell.Run strZip,,true

master.WriteLine strFileNameBase & " *************************************************************************************"

master.WriteLine strFileNameBase & " " & strzip &" was unziped in the zip archive Folder @" & now

master.WriteLine strFileNameBase & " *************************************************************************************"

Set WshShell = nothing

Else

master.WriteLine strFileNameBase & " **************************************************************************"

master.WriteLine strFileNameBase & " *********** ZIP FILE DOES NOT EXIST *****FAILURE******"

master.WriteLine strFileNameBase & " **************************************************************************"

Main = DTSTaskExecResult_Failure

End if

'-----------------------------------------------------------------------------------------------------------

'check to see if the file was successfully unzipped

'-----------------------------------------------------------------------------------------------------------

If fso.FileExists(strZipArchiveDir & "\" & strFileNameBase & ".bak") then

master.WriteLine strFileNameBase & " *********************************************************************************"

master.WriteLine strFileNameBase & " found unzipped file @" & now

master.WriteLine strFileNameBase & " BAK FILE EXISTS - SUCCESS D1 "

master.WriteLine strFileNameBase & " *********************************************************************************"

master.WriteLine

'-----------------------------------------------------------------------------------------------------------

'make a copy of the file then delete old bak file

'-----------------------------------------------------------------------------------------------------------

master.WriteLine strFileNameBase & " *********************************************************************************"

master.WriteLine strFileNameBase & " preparing to copy " & strFileNameBase & ".bak to main.bak "

master.WriteLine strFileNameBase & " *********************************************************************************"

master.WriteLine

Set WshShell = CreateObject("WScript.Shell")

'*********************************************************************************"

used a batch file to copy file into another folder.

'*********************************************************************************"

'fso.copyfile strZipArchiveDir &"\"& strFileNameBase & ".bak", strZipArchiveDir &"\"& "main.bak",true

strzip = strDir & "\copy_main.bat"

WshShell.Run strzip,,true

Set WshShell = nothing

master.WriteLine strFileNameBase & "*********************************************************************"

master.WriteLine strFileNameBase & " " &strFileNameBase & ".bak copied as main.bak @" & now

master.WriteLine strFileNameBase & "**********************************************************************"

master.WriteLine strFileNameBase & " *********************************************************************************"

master.WriteLine strFileNameBase & " preparing to delete " & strFileNameBase & ".bak "

master.WriteLine strFileNameBase & " *********************************************************************************"

master.WriteLine

fso.DeleteFile strZipArchiveDir &"\"& strFileNameBase & ".bak"

master.WriteLine strFileNameBase & "*********************************************************************"

master.WriteLine strFileNameBase & " " & strFileNameBase & ".bak deleted @" & now

master.WriteLine strFileNameBase & "**********************************************************************"

Main = DTSTaskExecResult_Success

Else

master.WriteLine "****************************************************************************************"

master.WriteLine strFileNameBase & " *********** BAK FILE DOES NOT EXIST *****FAILURE******"

Main = DTSTaskExecResult_Failure

master.WriteLine "****************************************************************************************"

End if

master.WriteLine "FILE CLOSED @" & now

master.WriteLine "**********************************************************************************************************************************************************************************"

master.WriteLine

master.close

set fso = nothing

set objfso = nothing

set master = nothing

set objfsoFolder = nothing

End Function

Scott Faso Grasshopper Points: 17 More actions · Answer 8

Now that is a great example! Thanks, I am going to give this a try!