Create a ZIp file and Unzip it using DTS

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Create a ZIp file and Unzip it using DTS

Author

Topic

Harini
Starting Member

1 Post

Posted - 2005-10-02 : 06:55:45

Hi,
I need to first extract a table from a database into a text file. Then I need to create a zip file from that text file. I have done the first part but am stuck at the second. Can some one help me do it?

Also, could you tell me how to unzip a text file using DTS?

Thanks in advance.
Harini

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-10-02 : 08:02:39

One way to do it is to have a registered copy of Winzip on the machine and use their command-line utility. (download it from their site)
In sql you can start a command line command with xp_cmdshell.

igorblackbelt
Constraint Violating Yak Guru

407 Posts

Posted - 2005-10-03 : 12:36:55

I need to first extract a table from a database into a text file.
You can use DTS to do this with no special coding.

Then I need to create a zip file from that text file.
Use the command line utility on WinZip, just make sure you buy a license to install on your server.

To Unzip:

'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************

Function Main()

theday = Day(dtsglobalvariables("TodaysDate"))
theprevday = Day(dtsglobalvariables("YesterdaysDate"))
themonth = Month(dtsglobalvariables("TodaysDate"))
theyear = Year(dtsglobalvariables("TodaysDate"))
If Len(theday) = 1 Then theday = "0" & theday
If Len(theprevday) = 1 Then theprevday = "0" & theprevday
If Len(themonth) = 1 Then themonth = "0" & themonth
filetoget1 = "Test_"& theyear & themonth & theprevday &".zip"
filetoget2 = "Test_"& theyear & themonth & theprevday &".txt"

Const ForReading = 1, ForWriting = 2, ForAppending = 3
Dim fs, f
Set fs = CreateObject("Scripting.FileSystemObject")
set c = fs.createtextfile("e:\Inetpub\wwwroot\data\test_unzip.bat",true)
c.writeline ("e:\progra~1\winzip\wzunzip.exe -s""password"" -o e:\Inetpub\wwwroot\data\today\" & filetoget1 &" E:\Inetpub\wwwroot\archive")
c.writeline ("e:\progra~1\winzip\wzunzip.exe -s""password"" -o E:\Inetpub\wwwroot\data\today\" & filetoget1 &" E:\Inetpub\wwwroot\data\today")
c.close
Main = DTSTaskExecResult_Success
End Function

---

Thanks!
Igor.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources