Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

unzip files using DTS Expand / Collapse
Author
Message
Posted Tuesday, April 12, 2005 2:32 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, June 5, 2014 7:52 AM
Points: 454, Visits: 194
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.
Post #174174
Posted Tuesday, April 12, 2005 2:39 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, July 22, 2011 5:35 AM
Points: 1,758, Visits: 9
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
Post #174179
Posted Wednesday, April 13, 2005 12:24 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 21, 2007 11:17 PM
Points: 1, Visits: 1

Hi there,

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

Micha

Post #174255
Posted Wednesday, April 13, 2005 1:50 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 10, 2013 12:34 PM
Points: 142, Visits: 132

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

 

Post #174270
Posted Wednesday, April 13, 2005 3:53 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 28, 2014 5:25 AM
Points: 15, Visits: 352

Hi people

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

 




Post #174297
Posted Wednesday, April 13, 2005 6:33 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, November 12, 2010 8:45 AM
Points: 107, Visits: 62

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

 




Post #174353
Posted Wednesday, April 13, 2005 8:21 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, April 21, 2014 12:49 PM
Points: 31, Visits: 17

gzip is free.

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

Post #174404
Posted Wednesday, April 13, 2005 9:00 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 7, 2014 12:43 PM
Points: 26, Visits: 55

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
  end if
 next
 Set fso      = CreateObject("Scripting.FileSystemObject")
 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.WriteLine

 master.close


 set fso = nothing
 set objfso = nothing
 set master = nothing
 set objfsoFolder = nothing

End Function




Post #174431
Posted Monday, May 2, 2005 7:28 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 25, 2008 8:50 AM
Points: 3, Visits: 4
Now that is a great example! Thanks, I am going to give this a try!
Post #179370
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse