Solved

how to Unzip files in DTS package

Posted on 2004-03-23
23
1,556 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
Is Your DevOps Pipeline Leaking?

Is your CI/CD pipeline a hodge-podge of randomly connected tools? You’ve likely got a tool to fix one problem & then a different tool to fix another, resulting in a cluster of tools with overlapping functionality. Learn how to optimize your pipeline with Gartner's recommendations

 

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 300 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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

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…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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…

734 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