Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Import/Export (DTS) and Replication (2000)
 Winzip in scheduled DTS Package

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}
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-03-20 : 12:42:41
Well, what error are you getting?

Tara
Go to Top of Page

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.

Go to Top of Page

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
Go to Top of Page

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

Go to Top of Page

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
Go to Top of Page

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

And the Parmaters line is -o "\\FilePath\File.zip" "\\Filepath\"

I have looked at this for WAY too long now!

Go to Top of Page

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
Go to Top of Page

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

Go to Top of Page

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
Go to Top of Page

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.

Go to Top of Page

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 thought

Brett

8-)
Go to Top of Page

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.

Go to Top of Page

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.



Brett

8-)
Go to Top of Page

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
Go to Top of Page

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

Go to Top of Page

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

Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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 Hegedusich
Senior Web Developer
IIMAK
http://www.iimak.com
My views are not necessarily my own.
Go to Top of Page
    Next Page

- Advertisement -