November 19, 2003 at 6:16 am
I have a DTS ActiveX which creates Excel extracts (using a template) then puts these extracts on an Intranet Server.
Ideally I would also like to Winzip these Excel extracts. I know how to do this in Access VBA, is it possible to do in DTS?
November 19, 2003 at 9:48 am
you can use the command line version of pkzip to do this. See the program for switches. If you want a component and object model, then you probably have to purchase a component to do this.
Steve Jones
http://qa.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://qa.sqlservercentral.com/bestof/
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
November 20, 2003 at 3:08 am
In Access I have quite a bit of code which ultimately run a command line something like the following:
C:\Program Files\WinZip\winzip32 -min -a C:\temp\temp.zip C:\temp\temp.xls
How would I call the same command line from ActiveX in DTS?
Also my code in Access detects when Winzip has finished and waits before zipping the next file. Is there a way of achieving this in DTS?
Has anyone created a large batch of zip files in DTS?
November 20, 2003 at 7:21 am
you have to install the command line tool of WINZIP first. Then try
declare @xlsfile varchar(255), @zipfile varchar(255), @cmdshell varchar(255)
set @xlsfile = 'your xls file'
set @zipfile = 'your output zip file'
set @cmdshell = 'C:\Progra~1\WinZip\WZZIP.EXE -a ' + @zipfile + ' ' + @xlsfile
exec xp_cmdshell @cmdshell
If haven't tried this with a batch but it should work as well.
November 20, 2003 at 8:05 am
Wunderbar!
Just one small question, I used:
set @cmdshell = 'C:\Progra~1\WinZip\WINZIP32.EXE -a ' + @zipfile + ' ' + @xlsfile
This seems to work perfectly. Would there be an advantage installing the command line version. I found a link for this at http://www.winzip.com/wzcline.htm
November 20, 2003 at 8:22 am
quote:
This seems to work perfectly. Would there be an advantage installing the command line version. I found a link for this at http://www.winzip.com/wzcline.htm
I got a problem to run the DTS package with the agent when I used the win version.
Therefore I used the command line version.
Matthias
November 20, 2003 at 8:29 am
Thanks, will download command version to be on the safe side
Stefan
November 26, 2003 at 4:03 am
I struggled a bit getting this to work in a loop in DTS with a changing file names. In the end created a stored procedure below and called this from a DTS Task 'Exec procZipFile ?,?' storing the file names in global variables.
Only problem I have, is where I make a mistake in the file names. The zip file is not created but there is no error, the DTS package appears to complete successfully. Is it possible to pick this up?
CREATE PROCEDURE procZipFile
@IncomingFile varchar(255) = null,
@Zipfile varchar(255) = null
AS
declare @cmdshell varchar(255)
set @cmdshell = 'C:\Progra~1\WinZip\WZZIP.EXE -m ' + @zipfile + ' ' + @IncomingFile
exec master..xp_cmdshell @cmdshell
November 26, 2003 at 9:24 am
The variables for the files have to contain the complete file name including the path.
Matthias
November 26, 2003 at 10:12 am
Yes I appreciate that. My question was, given I make a mistake e.g. I run on a server which can not see the path because it is on another server, how do I pick up the error in DTS, or some other unexpected error. How do I pick this up in DTS?
November 26, 2003 at 10:40 am
Use something like that:
EXEC @result = xp_cmdshell @cmdshell
IF (@result = 0)
PRINT 'Success'
ELSE
PRINT 'Failure'
I tried this with WZZIP and wrong file names. It works.
Matthias
November 27, 2003 at 3:52 am
Matthias, sorry if I am missing the obvious.
I can see how this works if I run the procedure from Query Analyzer, I see the result in the messages pane. But if I run the procedure from a DTS task, I can run the package and get no indication that the procedure has failed.
November 27, 2003 at 5:19 am
I had the same problem when I executed a DTS package from a DTS package. The outer package terminated succesfully even when the inner package created an error.
The solution to that is that in the properties of the inner package the
check box for "Fail package on first error" on the Logging page has to be ticked.
But I don't know if it works with a SP.
Matthias
November 27, 2003 at 6:32 am
It doesn't work with an SP
December 1, 2003 at 3:05 pm
Couldn't you return an Output Parameter from the stored Proc with the status Matthias described? Then evaluated the status in DTS (set the output parameter to a Global Variable), and write the error to a log file and continue looping...or set the package to fail with an ActiveX script
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply