SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Import/Export (DTS) and Replication (2000)
 Winzip in scheduled DTS Package
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

SQLDiva
Starting Member

USA
26 Posts

Posted - 03/20/2003 :  12:18:34  Show Profile  Send SQLDiva a Yahoo! Message  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 03/20/2003 :  12:42:24  Show Profile  Reply with Quote
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

USA
37142 Posts

Posted - 03/20/2003 :  12:42:41  Show Profile  Visit tkizer's Homepage  Reply with Quote
Well, what error are you getting?

Tara
Go to Top of Page

SQLDiva
Starting Member

USA
26 Posts

Posted - 03/20/2003 :  13:22:38  Show Profile  Send SQLDiva a Yahoo! Message  Reply with Quote
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

USA
37142 Posts

Posted - 03/20/2003 :  13:27:42  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

USA
26 Posts

Posted - 03/20/2003 :  13:49:58  Show Profile  Send SQLDiva a Yahoo! Message  Reply with Quote
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

USA
37142 Posts

Posted - 03/20/2003 :  13:53:41  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

USA
26 Posts

Posted - 03/20/2003 :  13:58:27  Show Profile  Send SQLDiva a Yahoo! Message  Reply with Quote
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

USA
37142 Posts

Posted - 03/20/2003 :  14:05:00  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

USA
26 Posts

Posted - 03/20/2003 :  14:08:59  Show Profile  Send SQLDiva a Yahoo! Message  Reply with Quote
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

USA
37142 Posts

Posted - 03/20/2003 :  14:33:06  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

USA
26 Posts

Posted - 03/20/2003 :  16:08:32  Show Profile  Send SQLDiva a Yahoo! Message  Reply with 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.

Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 03/20/2003 :  16:09:41  Show Profile  Reply with Quote
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

USA
26 Posts

Posted - 03/20/2003 :  16:11:18  Show Profile  Send SQLDiva a Yahoo! Message  Reply with Quote
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 - 03/20/2003 :  16:26:33  Show Profile  Reply with Quote
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

USA
37142 Posts

Posted - 03/20/2003 :  16:29:14  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

USA
26 Posts

Posted - 03/20/2003 :  16:30:16  Show Profile  Send SQLDiva a Yahoo! Message  Reply with Quote
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

USA
26 Posts

Posted - 03/20/2003 :  16:31:46  Show Profile  Send SQLDiva a Yahoo! Message  Reply with Quote
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

USA
37142 Posts

Posted - 03/20/2003 :  16:38:51  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

USA
37142 Posts

Posted - 03/20/2003 :  16:48:24  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

USA
18 Posts

Posted - 04/14/2003 :  08:02:44  Show Profile  Visit philh's Homepage  Reply with Quote
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
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000