Scheduled DTS Package Hangs on Create Zip

Question

Scheduled DTS Package Hangs on Create Zip

Andrew Barton

SSC-Addicted

Points: 490
More actions
October 9, 2006 at 4:41 am

#169974

Hi,
I hope somebody help me. I have a DTS package that creates a text file and then converts the text file to a zip file that is later emailed to a user. This all works fine when the dts package is run from EM but if i schedule the package to run it gets to the step that creates the zip file and hangs with Step Status 0 and no start or end date for the step. Could this be a permission problem or does the SQL Agent not like console windows. I am using Winzip command line to create the zip file. I have also turned on logging for the DTS Package and checked the log and it stops when it gets to the CreateZip step. I have tried looking on the web but can't find any information on this problem.
Thank you

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

lucky-80472 SSCertifiable Points: 7793 More actions · Answer 1

Hello Andrew,

It would be better if you can schedule the steps for zipping the file and then mail the file to the user in a stored proc and execute this stored proc as part of a job step. I think the reason might be the zipping process is done after transfering the control to the command window through xp_cmdshell but how can it transfer back to SQL Server.

Hope this helps you.

Thanks

Lucky

Paul Smith-221741 SSCrazy Points: 2003 More actions · Answer 2

Hi There

This might sound silly, but when I had this problem it was because the version of winzip that was installed had not been registered with a valid key.

Try logging onto the console of the server using the SQL Service Account and running your Zip component via query analyser.

If that still hangs. try via the command window (again using the SQL service account credentials).

My 2 hapennyworth

Paul

Mike Chappell Old Hand Points: 390 More actions · Answer 3

We had something similar once and it turned out the the program was waiting for something to happen on the Server. In the end we had to log on with the system account, open up the program and tick the box on the opening splash screen saying "do not display this screen again".

Or it could be a opening wizard or something like that popping up. The worst one I saw was years ago where we had to open up Access (don't ask!) as part of a job and that ruddy paperclip was nagging!

Of course, if you are using the command line version and not winzip this is probably less likely but I thought it might be worth mentioning because that had us going for hours!

Mike

Erich Brinker SSCrazy Points: 2973 More actions · Answer 4

I have used similar packages with DTS, but we had to end up going with a tool like PKZip which will work from commandline. We had similar issue where package would hang when it got to the zip task.

Andrew Barton SSC-Addicted Points: 490 More actions · Answer 5

It was the registration key. Our ops team hadn't registered winzip on the server. It is now working correctly and I now need to go and give our ops team a kick up the backside, thank you all for your help.