dhamijap
asked on
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.
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.
ASKER
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?
Suppose my file name is C:\temp\abc.zip and I want it unzipped in c:\temp2\ directory what will I do?
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
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
ASKER
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\AC NMDSDF.ZIP G:\SYSTEMS\TDLinx\InputFil es\'" command line?
In which TASK?
I have raised the points to 150
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\AC
In which TASK?
I have raised the points to 150
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....
If you run winzip32 -? from a command prompt and it tells you invalid option, you don't have the command line add in installed....
ASKER
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
pkd
it's not located under stored procedures--did you look under extended stored procedures in the master database?
ASKER
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\AC NMDSDF.ZIP G:\SYSTEMS\TDLinx\InputFil es\'
Server: Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'xp_cmdshell'.
EXEC xp_cmdshell 'C:\Program Files\Winzip\winzip32 -e G:\SYSTEMS\TDLinx\FtpIn\AC
Server: Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'xp_cmdshell'.
you have to prefix it with master:
EXEC master..xp_cmdshell 'C:\Program Files\Winzip\winzip32 -e G:\SYSTEMS\TDLinx\FtpIn\AC NMDSDF.ZIP G:\SYSTEMS\TDLinx\InputFil es\'
EXEC master..xp_cmdshell 'C:\Program Files\Winzip\winzip32 -e G:\SYSTEMS\TDLinx\FtpIn\AC
ASKER
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.
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.
What can't you get? Any error messages?
ASKER
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?
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?
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\AC NMDSDF.ZIP G:\SYSTEMS\TDLinx\InputFil es\'
What messages are returned?
EXEC master..xp_cmdshell 'C:\Program Files\Winzip\winzip32 -e G:\SYSTEMS\TDLinx\FtpIn\AC
What messages are returned?
ASKER
This is what I got.
'C:\Program' is not recognized as an internal or external command, operable program or batch file.
NULL
'C:\Program' is not recognized as an internal or external command, operable program or batch file.
NULL
Ahhhh, it's cause of the long file names....
Try this:
EXEC master..xp_cmdshell 'C:\Progra~1\Winzip\winzip 32 -e G:\SYSTEMS\TDLinx\FtpIn\AC NMDSDF.ZIP G:\SYSTEMS\TDLinx\InputFil es\'
Brett
Try this:
EXEC master..xp_cmdshell 'C:\Progra~1\Winzip\winzip
Brett
ASKER
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!
In out example above does "G" drive have NOT to be local? Instead it can be a linked file. Right!
ASKER
Here is what i know now:
I tried the follwong command at C:\ prompt at SQL server and it works.
C:\Progra~1\Winzip\winzip3 2 -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\winzip 32 -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
I tried the follwong command at C:\ prompt at SQL server and it works.
C:\Progra~1\Winzip\winzip3
If I try the following in QA it hangs:
EXEC master..xp_cmdshell 'C:\Progra~1\Winzip\winzip
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
If you're going to schedule the job, you can't use mapped drives--you have to use the unc path....
ASKER
Then I cant do it. I will be running it in DTS package which will be scheduled.
Can I do in Activex Script Task?
Can I do in Activex Script Task?
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........
ASKER
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
dhamijap
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....