August 20, 2008 at 2:39 pm
Would someone tell me what I need in order to ZIP a file within a DTS package? I am open to using ActiveX though I am not very good at it so I need as many details as possible.
I tried an Execute Task Properties with the following:
Description: zip the xls
Win32 process: C:\Program Files\WinZip\WINZIP32.EXE
Parameters: c:\temp\claims.xls c:\temp\claims.zip
Return Code: 0
Timeout: 0
When I execute just this step, I get the WinZip pop-up that states:
Multiple files were dropped, and one or more is an archive. Add files to archive?
I'm not sure what to answer here, nor how to go about doing it. I have found quite a few sites that have the -min -a etc. that should prefix the filenames but those give me various errors.
A co-worker mentioned using VB ActiveX script but I'm not sure about that either.
Any ideas are GREATLy appreciated. I've been out of the SQL Server 2000 world for a few years and back then I didn't have to do things like this?
Thanks!
Carol
August 20, 2008 at 3:50 pm
You can make use of xp_cmdshell under execute sql task under dts package:
cd "directory path where file that need to be zipped is placed"
"C:\Program Files\WinZip\wzzip.exe" -sPassword -ee -yc zipfilename.zip filethatneedstobezippedname
MJ
August 20, 2008 at 4:01 pm
I think your problem was passing the parameters.
Google "running winzip from command line" and look at the command line parameters and you should see the correct syntax for running winzip.
From that point, you can use xp_cmdshell in a SQL task (as someone already suggested), a Process task, or within an ActiveX task.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
August 20, 2008 at 4:11 pm
Hi there!!!
Many thanks to your reply but unfortunately this is not working either.
I am rather new to using SQL Server 2000(or rather, I haven't touched it in over 3 years other than a quick query here and there) but I have provided the following within the EXECUTE SQL TASK's SQL Statement's box:
cd "\\dsfile\abc\def\other"
"C:\Program Files\WinZip\WINZIP32.EXE" -sPassword -ee -yc Claims.zip Claims.xls
When I try to parse the query, the following error appears:
Error Source: Microsoft OLE DB Provider for SQL Server
Error Desription: Deferred prepare could not be completed.
Statement(s) could not be prepared.
Line2: Incorrect syntax near 'C:\Program Files\WinZip\WINZIP32.EXE
August 20, 2008 at 4:31 pm
Alvin Ramard (8/20/2008)
I think your problem was passing the parameters.Google "running winzip from command line" and look at the command line parameters and you should see the correct syntax for running winzip.
From that point, you can use xp_cmdshell in a SQL task (as someone already suggested), a Process task, or within an ActiveX task.
HI Alvin,
I have googled my fingers to the bone...I have tried everything...and nothing seems to work so I am sure that I'm doing something wrong. My knowledge with SQL Server is very limited so detailed explanations are truly welcome. My last effort before posting to the forum was using EXECUTE PROCESS TASK PROPERTIES. I just tried the parms: -a -en C:\temp\Claims.xls C:\temp\Claims.zip and when the single step was executed, the error message was: "Cannot open file; it does not appear to be in a valid archive. If you downloaded this file, try dowloading the file again."
If I change the -a to be -a+ as the WinZip Help Menu suggests for removing the archive attribute, then I get the error: WinZip parameter validation error. When I click the OK button, WinZip appears ready for me to do the zipping manually. This tells me that at least I am calling the correct program but it's not finding my files???
I'm not sure what else needs to be done, nor do I understand a lot of it for many of these posts respond thinking that the poster knows what a shellscript exe is. 🙁
August 20, 2008 at 4:52 pm
August 20, 2008 at 5:35 pm
Here's something I wrote a while back. This syntax gets used over 300 times per week so I guess it's well tested.
declare @Command as varchar(5000)
set @Command = 'C:\progra~1\winzip\winzip32 -min -e -o "K:\txtfiles\myFile.zip" "K:\txtfiles\"'
exec master.dbo.xp_cmdshell @Command
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
August 20, 2008 at 10:16 pm
Thank you very much for your response...unfortunately this states that it's successful, but nothing happens...it's probably something that I'm doing wrong...where should this be located? I put it in the EXECUTE SQL TASK SQL window...is this correct? I cannot use specific drive letters for when the SQL Job runs, it will be on a different computer with different mappings...
declare @Command as varchar(5000)
set @Command = 'C:\Program Files\WinZip\WINZIP32.EXE -min -e -o
"\\aaa\bbb\ccc\ddd\CLAIMS.xls" "\\\\aaa\bbb\ccc\ddd\CLAIMS.zip"'
exec master.dbo.xp_cmdshell @Command
I even tried using the parms in my Execute Process Task, but that pops up WinZip and then nothing happens...I cannot believe something this simple is so complex... 🙁
all help is GREATLY appreciated!!!
Thank you!
Carol
August 21, 2008 at 6:55 am
MANU (8/20/2008)
Go thru this link once:http://www.databasejournal.com/features/mssql/article.php/3556461
HTH
MJ
Hi there,
There is an awful lot of installation instructions here. I do not have access to that sort of set up at all. I basically have the ability to run WinZip and possibly another internal to Windows zip/compression type. I'll go through it hoping that there is something useful. I dont' have much time with this. I've been "fighting" with it for about 2 days now.
August 21, 2008 at 7:14 am
I would try running the SQL code in query analyzer just to see if that makes a difference. If that works try it in a SQL task in your DTS package. If it works in Query Analyzer but not in the DTS package then there's an issue with the DTS. Not sure what it would be.
Part of your problem may be Winzip itself. I remember something liker this about 3-4 years ago. I found something like a command line utility (free download) on the Winzip website. That's something you might want to take a look at.
Good luck.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
August 21, 2008 at 7:16 am
callain (8/20/2008)
Thank you very much for your response...unfortunately this states that it's successful, but nothing happens...it's probably something that I'm doing wrong...where should this be located? I put it in the EXECUTE SQL TASK SQL window...is this correct? I cannot use specific drive letters for when the SQL Job runs, it will be on a different computer with different mappings...declare @Command as varchar(5000)
set @Command = 'C:\Program Files\WinZip\WINZIP32.EXE -min -e -o
"\\aaa\bbb\ccc\ddd\CLAIMS.xls" "\\\\aaa\bbb\ccc\ddd\CLAIMS.zip"'
exec master.dbo.xp_cmdshell @Command
I even tried using the parms in my Execute Process Task, but that pops up WinZip and then nothing happens...I cannot believe something this simple is so complex... 🙁
all help is GREATLY appreciated!!!
Thank you!
Carol
hmmmm .... might the \\\\ be causing a problem? 4 instead of 2?
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
August 21, 2008 at 9:00 am
Alvin Ramard (8/21/2008)
callain (8/20/2008)
Thank you very much for your response...unfortunately this states that it's successful, but nothing happens...it's probably something that I'm doing wrong...where should this be located? I put it in the EXECUTE SQL TASK SQL window...is this correct? I cannot use specific drive letters for when the SQL Job runs, it will be on a different computer with different mappings...declare @Command as varchar(5000)
set @Command = 'C:\Program Files\WinZip\WINZIP32.EXE -min -e -o
"\\aaa\bbb\ccc\ddd\CLAIMS.xls" "\\\\aaa\bbb\ccc\ddd\CLAIMS.zip"'
exec master.dbo.xp_cmdshell @Command
I even tried using the parms in my Execute Process Task, but that pops up WinZip and then nothing happens...I cannot believe something this simple is so complex... 🙁
all help is GREATLY appreciated!!!
Thank you!
Carol
hmmmm .... might the \\\\ be causing a problem? 4 instead of 2?
no, that was just a copy/paste/rename error though I did run the query through SQL Analyzer to find that it doesn't like: 'C:\Program' is not recognized as an internal or external command,
Not sure what that means and I don't have access to look at the computer's files in question let alone change them... it but maybe it will provide me with a few other things to try or at least give my IT support person something to look into.
Thanks!
August 21, 2008 at 9:02 am
Alvin Ramard (8/21/2008)
I would try running the SQL code in query analyzer just to see if that makes a difference. If that works try it in a SQL task in your DTS package. If it works in Query Analyzer but not in the DTS package then there's an issue with the DTS. Not sure what it would be.Part of your problem may be Winzip itself. I remember something liker this about 3-4 years ago. I found something like a command line utility (free download) on the Winzip website. That's something you might want to take a look at.
Good luck.
Hmmmm...GREAT idea...I tried that and got an error: 'C:\Program' is not recognized as an internal or external command,
Not sure what it means. See my response to Alvin regarding my inability to see anything on the server where SS2k is loaded.
Thanks!
August 21, 2008 at 9:07 am
Try putting double quotes around C:\Program Files\WinZip\WINZIP32.EXE
Looks like the space between Program and Files is a problem.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
August 21, 2008 at 3:17 pm
Alvin Ramard (8/21/2008)
I would try running the SQL code in query analyzer just to see if that makes a difference. If that works try it in a SQL task in your DTS package. If it works in Query Analyzer but not in the DTS package then there's an issue with the DTS. Not sure what it would be.Part of your problem may be Winzip itself. I remember something liker this about 3-4 years ago. I found something like a command line utility (free download) on the Winzip website. That's something you might want to take a look at.
Good luck.
Hi Alvin,
I did try and did receive an error. I think my first problem is that I'm using Citrix to get to the data and hardware where SQL Server resides. I have confirmed that while WinZip is on my Citrix-version, it does not seem to be on the actual SS box. I have had my IT dept look into it. Thanks!
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply