Hi there,

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

Micha

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

Hi people

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

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

gzip is free.

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

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