Zipping a file

  • 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?

  • 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

    sjones@sqlservercentral.com

    http://www.sqlservercentral.com/columnists/sjones

    The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/

    http://www.dkranch.net

  • 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?

  • 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.

  • 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

  • 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

  • Thanks, will download command version to be on the safe side

    Stefan

  • 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

  • The variables for the files have to contain the complete file name including the path.

    Matthias

  • 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?

  • 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

  • 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.

  • 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

  • It doesn't work with an SP

  • 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