Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

how to Unzip files in DTS package

Posted on 2004-03-23
23
Medium Priority
?
1,562 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

715 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