How to zip a text file in a DTS Task

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)

How to zip a text file in a DTS Task

Author

Topic

MxWebb
Starting Member

8 Posts

Posted - 2007-02-06 : 10:20:39

Here is what I am trying todo.

1. Run a query that writes the Results to a File Called AUX_Export_.Txt (I created a DTS to run on Schedule)

2. I then created a ActiveX Script to Rename this file to
AUX_Export_020507.txt You can see I am adding the current Days Month and two digits Day as two digits and year as two digits.

3. Now I need to Zip this text file and move it out to a FTP directory?????????????????????HELP
I have 7-zip which I should be able to call from the command line with: "C:/Program Files/7-Zip/7z.exe" a -tzip "R:\Avectra\Queries\Mike Queries\Reports\Commerce Register\ReadyForZip\*.txt"

Can someone tell me what kind of task I need to call or make to make a commend line call? Then I think all I need is a File Transfer Task to Move the zip file to a FTP directory..

Question can I create a global Variable That holds the file name that I Rename the text file to in Step 2? That way I know I am calling the Zip file command with the correct file name instead of *.txt?zipping up.

Please give me details so I don't have to struggle.

Thanks
Michael Webb

MxWebb
Starting Member

8 Posts

Posted - 2007-02-06 : 17:23:22

WHAT NO HELP HERE?

Thanks
Michael Webb

ekb18c
Starting Member

18 Posts

Posted - 2007-02-07 : 07:44:33

You need to use the win32 task in DTS to zip the text file or active script to do that. Up to you, as you can use either or.

activex example:

Set shell = CreateObject("WScript.Shell")
Shell.run "cmd /K C:/Program Files/7-Zip/7z.exe" a -tzip R:\Avectra\Queries\Mike Queries\Reports\Commerce Register\ReadyForZip\*.txt"
Set shell = Nothing

Then you need to use activex script to FTP the file over to the server. I use Cuteftp because they have a simple API and it's very easy to code in activex.

www.sqlnerd.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources