March 10, 2011 at 10:36 am
I have a T-SQL Task set up to use a Winzip command line for zipping a file. There are no parameters going into the task, but there is a single row result set up to get the name of the zip file and pass it to the next task. Unfortunately, I get a Winzip message instead of my file name. It's driving me NUTS.
Here's the code:
DECLARE @ServerName varchar(100), @Date datetime, @String varchar(1000);
SELECT @ServerName = '\\MyServer\MyShare_dev';
SELECT@date = getdate();
SELECT@String = '"D:\Program Files\Winzip\wzzip.exe" -m ' + @ServerName
+ '\Folder1\Processed\Group\Item\Details' + '_' + CAST(YEAR(@date)*10000 + MONTH(@date)*100 + DAY(@date) AS NCHAR(8))
+ CASE Len(CAST(Datepart(Hh,@date)*100 + Datepart(mi,@date) AS NVarCHAR(4)) ) When 3 Then '0' Else '' End
+ CAST(Datepart(Hh,@date)*100 + Datepart(mi,@date) AS NVarCHAR(4))
+ '.zip @'+ @ServerName + '\Folder1\Processed\Group\Item\Details.lst';
EXEC xp_cmdshell @String, no_output;
SELECT Substring(@String,40,102) AS ZipFileName;
Now, I have tried switching the last two commands around, doing the Exec first and then the SELECT and vice versa. It doesn't seem to matter. My string variable keeps ending up with this value:
SSIS MsgBox
WinZip(R) Command Line Support Add-On Version 1.1 SR-1 (Build 6224)
Thing is, I run this script in SSMS and I end up with the correct value in my SELECT. But as soon as I'm running it in SSIS, I'm getting output that is throwing itself in my result variable and overwriting my file name.
Does anyone know how I can fix this?
March 10, 2011 at 11:01 am
Random thoughts...
Curious, what is @ doing in your command line? This is what I get when I run your code:
"D:\Program Files\Winzip\wzzip.exe" -m \\MyServer\MyShare_dev\Folder1\Processed\Group\Item\Details_201103101058.zip @\\MyServer\MyShare_dev\Folder1\Processed\Group\Item\Details.lst
Just a hunch...try surrounding your file name paths with quotes in case a space is introduced into them by one of your parameters.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 10, 2011 at 11:15 am
Brandie Tarvin (3/10/2011)
... I run this script in SSMS and I end up with the correct value in my SELECT. But as soon as I'm running it in SSIS, I'm getting output that is throwing itself in my result variable and overwriting my file name.Does anyone know how I can fix this?
Are you using the same authentication that SSIS is using? You might have to log in with the SSIS credentials and run winzip once to set things up for that login.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
March 10, 2011 at 12:03 pm
WayneS might be on to something. Is your copy registered?
I just setup wzzip to test locally and here is what I get the first time I try running it:
C:\Program Files\WinZip>WZZIP.EXE -m \\us\home\Downloads\1.zip @\\us\Downloads\1.lst
WinZip(R) Command Line Support Add-On Version 3.2 (Build 8668)
Copyright (c) 1991-2009 WinZip International LLC - All Rights Reserved
THANK YOU FOR TRYING WINZIP COMMAND LINE ADD-ON
This is a fully functional version for EVALUATION USE ONLY
This notice is not displayed with registered Standard and Pro editions of
WinZip.
Please go to http://www.winzip.com to order WinZip.
(press any key to continue (Ctrl-C to quit))
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 11, 2011 at 5:00 am
opc.three (3/10/2011)
Random thoughts...Curious, what is @ doing in your command line? This is what I get when I run your code:
"D:\Program Files\Winzip\wzzip.exe" -m \\MyServer\MyShare_dev\Folder1\Processed\Group\Item\Details_201103101058.zip @\\MyServer\MyShare_dev\Folder1\Processed\Group\Item\Details.lst
Just a hunch...try surrounding your file name paths with quotes in case a space is introduced into them by one of your parameters.
Quotes won't work. The @ is telling Winzip to look at a .lst file to pull all the file names in that file into the zip.
March 11, 2011 at 5:12 am
lijnenjoost (3/11/2011)
Why don't you use a Script Task to unzip or to zip your files in SSIS. That way you don't need a zip tool and you don't get unexpected situations when winzip suddenly gives a messagebox or something.
I appreciate the thought, but I'm trying to avoid adding additional tasks into the package if I don't have to. If I were to do that, I'd just use another Execute SQL task to read the directory and grab the max zip package name (which is doable and will work, but doesn't resolve the issue I want to resolve).
To answer earlier questions, yes, my copy of Winzip is registered. EDIT: Wayne, I'm running this in BIDS. So far as I can tell, it's using my credentials (which are the same as the ones I'm using in SSMS and in remote desktop for a direct login to the server). But I will verify that.
If I go to a CMD window. I will get a Winzip header message:
Cmd Prompt
WinZip(R) Command Line Support Add-On Version 1.1 SR-1 (Build 6224)Copyright (c) WinZip Computing, Inc. 1991-2004 - All Rights Reserved
Adding Details.xls
creating Zip file \\MyServer\MyShare_dev\Folder1\Processed\Group\Item\Details_201103110706.zip
Moving Files...
So, apparently my "no output" statement doesn't completely kill the proprietary information in SSIS, even if I don't see it in SSMS (which I don't). So why does the result still get overridden if I move the SELECT statement above the Exec statement?
I'd really like to get this all done in one task if possible, rather than adding additional tasks to the package.
March 11, 2011 at 7:11 am
Well, running from BIDS is different from running from SSIS - I assumed that you meant the agent was getting this message.
A couple of possibilities:
1. Use 7Zip.
2. CozyRoc has a Zip Task in their SSIS add-on package suite. See http://www.cozyroc.com/
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
March 11, 2011 at 9:55 am
Much as I'd love to use one of those two options, we can't use unapproved software or components.
However, I am allowed to write up SSIS components myself... If I had a clue how to develop such things.
Now there's an idea. Maybe I should learn how to build these things myself.
March 11, 2011 at 10:00 am
Brandi I'd hope it would be easy to get a free application on the approved list easily;
I've got to recommend 7zip's command line version, i use it a lot.
here's a script sample with lots of comments for ease of understanding:
--http://www.7-zip.org/download.html
--http://downloads.sourceforge.net/sevenzip/7za920.zip
DECLARE @results TABLE(results varchar(255))
declare @command varchar(2000)
--zip two backup files
SET @command =
'"C:\Data\7zip_CommandLine_7za465\' --path to 7za command line utility note the dbl quotes for long file names!
+ '7za.exe"' --the exe: i'm using the command line utility.
+ ' a ' --the Add command: add to zip file:
+ '"C:\Data\' --path for zip
+ 'myZipFile.zip"' --zip file name, note the dbl quotes for long file names!
+ ' ' --whitespace between zip file and file to add
+ '"C:\DB\' --path for the file(s) to add
+ 'SandBox2011-03-09.bak"' --the file
+ ' ' --whitespace between first file and next file to add
+ '"C:\DB\' --path for the file(s) to add
+ 'SandBox2011-03-10.bak"' --the file
+ ' -y' --suppress any dialogs by answering yes to any and all prompts
print @command
insert into @results
exec xp_cmdshell @command
select * from @results
Lowell
March 11, 2011 at 10:13 am
No, Lowell. Not easy at all. And it's not about price. Approvals have to go through our Corporate office. Applications are rigorously testing to make sure they are compatible with existing software & hardware, as well as tested to make sure they don't violate our privacy policies and any laws (HIPAA, SOX, etc.).
That aside, if anyone can help me with my original problem, I'd really appreciate it. I have a couple of work arounds, but I'd like to solve the original issue (the result set not filling in properly) if I can.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply