• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1570
  • Last Modified:

how to Unzip files in DTS package

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
dhamijap
Asked:
dhamijap
  • 12
  • 11
1 Solution
 
arbertCommented:
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
 
dhamijapAuthor Commented:
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
 
arbertCommented:
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
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
dhamijapAuthor Commented:
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
 
arbertCommented:
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
 
dhamijapAuthor Commented:
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
 
arbertCommented:
it's not located under stored procedures--did you look under extended stored procedures in the master database?
0
 
dhamijapAuthor Commented:
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
 
arbertCommented:
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
 
dhamijapAuthor Commented:
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
 
arbertCommented:
What can't you get?   Any error messages?
0
 
dhamijapAuthor Commented:
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
 
arbertCommented:
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
 
dhamijapAuthor Commented:
This is what I got.
'C:\Program' is not recognized as an internal or external command, operable program or batch file.
NULL
0
 
arbertCommented:
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
 
dhamijapAuthor Commented:
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
 
dhamijapAuthor Commented:
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
 
arbertCommented:
If you're going to schedule the job, you can't use mapped drives--you have to use the unc path....
0
 
dhamijapAuthor Commented:
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
 
arbertCommented:
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
 
dhamijapAuthor Commented:
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
 
arbertCommented:
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
 
dhamijapAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

  • 12
  • 11
Tackle projects and never again get stuck behind a technical roadblock.
Join Now