| Author |
Topic |
|
SQLDiva
Starting Member
26 Posts |
 Posted - 2003-03-20 : 12:18:34
|
| Has anyone ever been able to successfully unzip a set of files using a scheduled DTS Package?Within DTS, I am using an Execute Process Task to unzip the files. The DTS Package runs fine. When I schedule the dts package, it failes on the unzip step. Any ideas?Thanks,Ann |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2003-03-20 : 12:42:24
|
quote:
When I schedule the dts package, it failes on the unzip step.
Surely there is an error message associated with this failure ....Jay White{0} |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-03-20 : 12:42:41
|
| Well, what error are you getting?Tara |
|
|
|
SQLDiva
Starting Member
26 Posts |
Posted - 2003-03-20 : 13:22:38
|
| Actually, to call it a failure was actually the wrong terminology. It just never completes. It doesn't actually ISSUE a failure (unless I use set timeout feature...but then it just times out). It just runs FOREVER. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-03-20 : 13:27:42
|
| Have you tried recreating the job? How did you schedule it? Did you just right click on the DTS package to schedule it or did you create a step in the job that runs DTSRun.exe? Try it both ways and see if that works.Tara |
|
|
|
SQLDiva
Starting Member
26 Posts |
Posted - 2003-03-20 : 13:49:58
|
| I've tried both ways. It's not JUST in the job either. If I run the dts package using dtsrun.exe from query analyzer, i get the same result of the package running forever |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-03-20 : 13:53:41
|
| When you execute the DTS package just by clicking execute, it runs fine? Does it actually unzip the files? What is the command that you are using to unzip the files? I can go ahead and create a sample DTS package and test it out over here by executing it in various ways.Tara |
|
|
|
SQLDiva
Starting Member
26 Posts |
Posted - 2003-03-20 : 13:58:27
|
| Yes, the dts package executes fine if you right click to execute, or when you have it open and execute it.i am using the execute process task (although the same thing happens if I try to just use command line). The win32process line is C:\Program Files\WinZip\WZUNZIP.EXEAnd the Parmaters line is -o "\\FilePath\File.zip" "\\Filepath\"I have looked at this for WAY too long now! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-03-20 : 14:05:00
|
| I don't have WZUNZIP.exe although I have Winzip installed. I tried running WINZIP32.exe (even though I knew that probably isn't the one to call) and it asked me a question. Does WZUNZIP.exe ask you a question when just execute it from the DTS package? If so, your job will never complete because who is going to answer the question.Tara |
|
|
|
SQLDiva
Starting Member
26 Posts |
Posted - 2003-03-20 : 14:08:59
|
| WZUNZIP.exe is the add on for command line interface that I downloaded from winzip's site per instruction from their help file. No, it doesn't ask a question. Although when I run it from the dts package, it does pop up a DOS window, runs, then automatically closes. There is no intereaction with this window which is what is so maddening about this whole thing!Also, I am using a registered copy of Winzip that eliminates the need to press a button to continue in command line interface.Ann |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-03-20 : 14:33:06
|
| I wonder if the problem is with the DOS window opening up. I bet that when you schedule the job that the DOS window is launched (although you might never see it) and never closes, so the job thinks that it is still running. When you say that a DOS window opens up, is it cmd.exe that is running? If it is, then go ahead and run the job and see if cmd.exe ever closes by checking the Processes tab in Task Manager.Tara |
|
|
|
SQLDiva
Starting Member
26 Posts |
Posted - 2003-03-20 : 16:08:32
|
| yeah, cmd.exe is still running. What I'm going to do is use a windows job to execute a batch file to unzip, then have sql look for those files.I hate to do that, because I like to have all my sql junk wrapped up in one place so that if my db's are backed up, I would need to recover, but I'm going to have to do it this way until I figure out a better way. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-03-20 : 16:09:41
|
| Why not create a job that has 2 steps. First use master..xp_cmdshell to execute your winzip, then the second setp would be the bcp. If you wrap it in a sproc, you could have even more control.Just a thoughtBrett8-) |
|
|
|
SQLDiva
Starting Member
26 Posts |
Posted - 2003-03-20 : 16:11:18
|
| Brett - sounds like a great idea, but I tried it. xp_cmdshell gives me the same 'failure' as the Execute Process task. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-03-20 : 16:26:33
|
| ok, try this and let me know if it executes:master..xp_cmdshell 'Dir c:\*.*'A couple of things come to mind. Is the zip product you are using a GUI Zip? That might something, also I know DTS is funny, in that if You run it, it sees your local drive mappings. When it's scheduled it see it's mappings. I set up a mirrored environment on my box to match the D drive of my server and make sure all the file are copied every night (and I make sure they are in synch during the day).Oh, that was before I stopped using DTS entirely...seen too many buggy things.Brett8-) |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-03-20 : 16:29:14
|
quote:
yeah, cmd.exe is still running. What I'm going to do is use a windows job to execute a batch file to unzip, then have sql look for those files.I hate to do that, because I like to have all my sql junk wrapped up in one place so that if my db's are backed up, I would need to recover, but I'm going to have to do it this way until I figure out a better way.
Your plan sounds like a very good plan. I do not understand why cmd.exe does not close when it is being called from a job. How is the SQLSERVERAGENT configured? Domain account or local system account? If local system account, then do you have it setup to allow it to interact with the desktop (if so, that could be the problem)?Tara |
|
|
|
SQLDiva
Starting Member
26 Posts |
Posted - 2003-03-20 : 16:30:16
|
| Yes, it executes fine. No, I'm using Winzip with the command line executable downloaded. I've also tried WinRAR. I'm using the full path (\\servername\sharename\whatever.zip) instead of using drive letters.This is driving me crazy :)Ann |
|
|
|
SQLDiva
Starting Member
26 Posts |
Posted - 2003-03-20 : 16:31:46
|
quote:
Your plan sounds like a very good plan. I do not understand why cmd.exe does not close when it is being called from a job. How is the SQLSERVERAGENT configured? Domain account or local system account? If local system account, then do you have it setup to allow it to interact with the desktop (if so, that could be the problem)?Tara
Tara - It is using a Domain account with God-like priviledges. Crazy huh?Ann |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-03-20 : 16:38:51
|
| I'm at a loss for this then. In order to get around this problem, you'll have to create a batch file which will do the unzip and then call the batch file using xp_cmdshell.Tara |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-03-20 : 16:48:24
|
| I was thinking about this a little more, and...The job runs under the account that the SQLSERVERAGENT logs in as. Try logging into the server with this account and then executing the DTS package by just clicking execute. Does it work and does it ever ask for any information?Tara |
|
|
|
philh
Starting Member
18 Posts |
Posted - 2003-04-14 : 08:02:44
|
| We do this on a regular basis, but we exported the DTS code to a VB file. I have a separate script that reads a directory and performs the WZUNZIP on each file in the directory, and then calls the compiled DTS, which runs against the newly-unZIPPED files.No problems with this approach. Let me know if you need specifics.HTH,Phil HegedusichSenior Web DeveloperIIMAKhttp://www.iimak.comMy views are not necessarily my own. |
|
|
|
Next Page
|
Winzip in scheduled DTS Package