Hello everybody,
I have one XML file whcih is a zip file, which i do want to unzip using DTS
and then want to load XML into SQL SERVER table, so can u pls let me know,
how can i unzip file using DTS.

thx

I would call the cmdline version of Winzip or PKZip in an ExecuteProcess task.



--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - SQL Server 2005 Integration Services.
www.Konesans.com


"mvp" <mvp (AT) discussions (DOT) microsoft.com> wrote

Hi Allan,
Can you pls provide me small code example because i am very new in VB Script
coding. I have already installed cmdline version of winzip.. i do have my zip
file in C:\Temp\Test.zip, so pls provide me small example of activex script,
so that i can create activex task in DTS and test.

thx in advance


"Allan Mitchell" wrote:

Code on demand. - just happened to use this the other day.

strCommand = "c:\temp\pkunzip test.zip"
Set oShell = CreateObject("WScript.Shell")
oShell.Run strCommand
--
Jim Vierra

"mvp" <mvp (AT) discussions (DOT) microsoft.com> wrote

Thanks Jim - the code fairy <grin>

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - SQL Server 2005 Integration Services.
www.Konesans.com


"Jim Vierra" <jvierra (AT) msn (DOT) com> wrote

I couldn't resist - it was sitting on my desktop from a previous
mis-adventure so I just cut out the fat an voila'!

Ended up learning something too. Setting environment variables from script
is not documented but it can be done.

WshEnvironment("VARNAME") = "var_setting"

Won't persist outside the script scope but is useable for passing vars in a
script or for changing the environment for provider calls and to alter what
a spawned process see in the environment.
--
Jim Vierra

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

Yep.

I use environment variables al the time as something to pass between
environments so maybe

Database Name
Server Name


Things like that. Real Easy.


I addition to using WScript to do the execution we could use the
ExecuteProcess task


How to manipulate the Execute Process task.
(http://www.sqldts.com/default.aspx?251)




"Jim Vierra" <jvierra (AT) msn (DOT) com> wrote

Except you are in a DTS session. It will work and provides better control
than the shell method but will only work under DTS. The example I gave
should work under any script environment. Don't know if the user needed it
in DTS. The question seemed to come from outside the DTS scripting arena
and I didn't want to go down the long road of debugging a first time DTS
script.


--
Jim Vierra

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

Good point Jim, I still forget that just because we are in a DTS NG the
question could be about anywhere <grin>

I looked back to the original Q and saw this

"...I have one XML file whcih is a zip file, which i do want to unzip
using DTS..."




"Jim Vierra" <jvierra (AT) msn (DOT) com> wrote

I wasn't sure and didn't look back. The DTS method has more flexibility
too. The shell method can also be set up to capture the output of a spawned
program. I don't believe DTS can do this. It should be possible to
re-direct the output of zip using the ziplist command to feed records a lie
at a time back to the DTS package. You can pick up stderr, stdin, stdout as
separate streams.

--
Jim Vierra

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote