Solved

how to Unzip files in DTS package

Posted on 2004-03-23
23
1,543 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
 

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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

707 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now