Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

how to Unzip files in DTS package

Posted on 2004-03-23
23
Medium Priority
?
1,564 Views
Last Modified: 2013-11-30
Hi Experts:
I have a DTS package. In which I ftp 2 files in a directory. Then I manually unzip them in a second directory. Then those files I do Tranform Data Task to get them into tables.

I like to know if I can unzip those files in DTS package some how. Please provide sample code. This should be very easy as when I explored how to FTP in DTS? It only took 3-4 settings and I had the files. Please help thanks.
0
Comment
Question by:dhamijap
  • 12
  • 11
23 Comments
 
LVL 34

Expert Comment

by:arbert
ID: 10660314
If depends on the Zip package you're using.

Winzip has an "addin" that will let you unzip/zip from the command prompt:

http://www.winzip.com/downcl.htm

You can execute this with a Execute SQL task using xp_cmdshell....
0
 

Author Comment

by:dhamijap
ID: 10660354
Please tell me what I need to do?
Suppose my file name is C:\temp\abc.zip and I want it unzipped in c:\temp2\ directory what will I do?
0
 
LVL 34

Expert Comment

by:arbert
ID: 10660810
Like I said above--depends on what zip package you have--you never said....

If you are using winzip, you can add an execute sql task to your DTS package and do something like this:

xp_cmdshell 'winzip32 -e  c:\temp\abc.zip   c:\temp2\'


Brett
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

Author Comment

by:dhamijap
ID: 10661362
Brett:
 This is the first time I am doing it so if I miss anything please ask. I am using winzip32. I have the following questions:
Where should I find xp_cmdshell to make sure that it is there?
How do I know if winzip addons are installed on my server?
How exactly do i run this "xp_cmdshell 'winzip32 -e  G:\SYSTEMS\TDLinx\FtpIn\ACNMDSDF.ZIP  G:\SYSTEMS\TDLinx\InputFiles\'" command line?
In which TASK?
I have raised the points to 150
0
 
LVL 34

Expert Comment

by:arbert
ID: 10661657
Like I said above--add an EXECUTE SQL task to the DTS package....xp_cmdshell is an extendend stored proc that's located in the master database....

If you run winzip32 -?   from a command prompt and it tells you invalid option, you don't have the command line add in installed....
0
 

Author Comment

by:dhamijap
ID: 10661974
Well I rand winzip32 sucessfully but sp is not in master data base. I have questioned that to dba so I will let you know unless you know why I do not have it or how to get it?
pkd
0
 
LVL 34

Expert Comment

by:arbert
ID: 10662035
it's not located under stored procedures--did you look under extended stored procedures in the master database?
0
 

Author Comment

by:dhamijap
ID: 10667461
Yes, It is there in extended sps. I try to run at Query Analyzer and I get error saying:
EXEC xp_cmdshell 'C:\Program Files\Winzip\winzip32 -e  G:\SYSTEMS\TDLinx\FtpIn\ACNMDSDF.ZIP  G:\SYSTEMS\TDLinx\InputFiles\'
Server: Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'xp_cmdshell'.
0
 
LVL 34

Expert Comment

by:arbert
ID: 10668925
you have to prefix it with master:

EXEC master..xp_cmdshell 'C:\Program Files\Winzip\winzip32 -e  G:\SYSTEMS\TDLinx\FtpIn\ACNMDSDF.ZIP  G:\SYSTEMS\TDLinx\InputFiles\'
0
 

Author Comment

by:dhamijap
ID: 10687356
arbert:
I still could not get it. I want to doubled the points, if you can help me more step by step. Other wise I will accept your answer and close the question. If you have time please let me know.
0
 
LVL 34

Expert Comment

by:arbert
ID: 10688169
What can't you get?   Any error messages?
0
 

Author Comment

by:dhamijap
ID: 10690632
I have raised the points to 300

I like you to lead.
Here is what I need to know

Winzip 8.1 is installed on my sql server would this work?
Zipped file is sitting on a file server. Is is ok?
How can I test at lowest level?
0
 
LVL 34

Expert Comment

by:arbert
ID: 10690943
Yes, this should all work.  Open query analyzer and paste the statement above that you think should work:


EXEC master..xp_cmdshell 'C:\Program Files\Winzip\winzip32 -e  G:\SYSTEMS\TDLinx\FtpIn\ACNMDSDF.ZIP  G:\SYSTEMS\TDLinx\InputFiles\'


What messages are returned?
0
 

Author Comment

by:dhamijap
ID: 10691173
This is what I got.
'C:\Program' is not recognized as an internal or external command, operable program or batch file.
NULL
0
 
LVL 34

Expert Comment

by:arbert
ID: 10691905
Ahhhh, it's cause of the long file names....

Try this:

EXEC master..xp_cmdshell 'C:\Progra~1\Winzip\winzip32 -e  G:\SYSTEMS\TDLinx\FtpIn\ACNMDSDF.ZIP  G:\SYSTEMS\TDLinx\InputFiles\'



Brett


0
 

Author Comment

by:dhamijap
ID: 10703995
It does not give any errors but it locks up. I think it has something to do with my maping of G drive. I think it will work and I will let you know.

In out example above does "G" drive have NOT to be local? Instead it can be a linked file. Right!
0
 

Author Comment

by:dhamijap
ID: 10704120
Here is what i know now:

I tried the follwong command at C:\ prompt at SQL server and it works.
C:\Progra~1\Winzip\winzip32 -e  'S:\TDLinx\FtpIn\ABC.ZIP  S:\TDLinx\InputFiles\'

If I try the following in QA it hangs:
EXEC master..xp_cmdshell 'C:\Progra~1\Winzip\winzip32 -e  S:\TDLinx\FtpIn\ABC.ZIP  S:\TDLinx\InputFiles\'
and I have to kill it.

Actually G drive on my computer is "S" drive at SQL server. So, I changed and created a much smalled zip file to test called ABC.ZIP

0
 
LVL 34

Expert Comment

by:arbert
ID: 10705165
If you're going to schedule the job, you can't use mapped drives--you have to use the unc path....
0
 

Author Comment

by:dhamijap
ID: 10705288
Then I cant do it. I will be running it in DTS package which will be scheduled.
Can I do in Activex Script Task?
0
 
LVL 34

Expert Comment

by:arbert
ID: 10705866
You still have the same problem--mapped drives aren't recognized.  Why can't you use UNC?  If you can map the drive, you know the UNC path to it........
0
 

Author Comment

by:dhamijap
ID: 10705935
what is a UNC path? I am sure I can do it if I know how to do it? Can you tell me how? Please.
0
 
LVL 34

Accepted Solution

by:
arbert earned 1200 total points
ID: 10706641
UNC path is the exact server\folder patch to the file--like this:

\\servername\foldername\filename.zip

So, instead of s:\tdlinx\ftpin\abc.zip  you would have \\whatevertheservernameis\tdlinx\ftpin\abc.zip

Brett
0
 

Author Comment

by:dhamijap
ID: 10717790
Finally, I got them. For others who will visit this I just have to reinstall winzip and winzip addons and I got them working. Thank to arbert.
dhamijap
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

972 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question