asked on

DTS -- Passing Global Variable into Execute Process Task, Parameters field

Experts:

I have an ActiveX Script task that dynamically creates a global variable (DiffFileName), which is a path\filename string (example string=\\172.168.1.86\K$\myfile20040324.bak)

Next, I have an Execute Process Task which runs Winzip.

Winzip requires several params, one of which is a source path\filename.

How do I get DTS to pass the global variable into the Execute Process Task, Parameters field? (See Details below.)

THANKS!
--SW

DETAIL:

Win2000/SQL Server 2000

Actual Execute Process Task Values (using example source filename above):

Win32 Process: C:\Program Files\WinZip\WINZIP32.EXE
Parameters: -min -a -en "c:\OGL_cta_backup.zip" "DiffFileName"
Return Code: 0
Timeout: 0

Active X Task Script:
Function Main()
CurrentDate =now()
sFilename = "\\172.168.1.86\K$\myfile" & Right(Year(CurrentDate),4)
If Month(CurrentDate) < 10 Then sFilename = sFilename & "0" & _
Month(CurrentDate) Else sFilename = sFilename & Month(CurrentDate)
If Day(CurrentDate) < 10 Then sFilename = sFilename & _
"0" & Day(CurrentDate) Else sFilename = sFilename & Day(CurrentDate)
sFilename=sFilename&".bak"
DTSGlobalVariables("DiffFileName").Value = sFilename
Main = DTSTaskExecResult_Success
End Function

arbert

You will have to create a parsible SQL Statement first (so you can fake sql server into thinking it's a good SQL Statement). This will allow you to select your parms. After you select the parms you want to use, then you can change your SQL Statement back to the master..xp_cmdshell ?

See this link:

https://www.experts-exchange.com/questions/20909916/DTS-SQL-Execute-Task-with-global-variable.html

SOLUTION

adwiseman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dhenson

Rather than use the Execute Process Task... Would Shelling to the winzip exe from your Active X Task accomplish your goal?

Shell "C:\Program Files\WinZip\WINZIP32.EXE -min -a -en c:\OGL_cta_backup.zip " & sFilename

dhenson

sw7104

ASKER

dhenson:
Ran following script and got error:

Error Source: Microsoft VBScript runtime error
Error Descrtiption: Type mismatch: 'Shell'

--------------------------------------
Function Main()

sFilename = "\\172.168.1.86\K$\myfile20040324.bak"

Shell "C:\Program Files\WinZip\WINZIP32.EXE -min -a -en c:\OGL_cta_backup.zip " & sFilename

Main = DTSTaskExecResult_Success

End Function

adwiseman

Shell is not available in DTS.

dhenson

ahh... oops.

my bad

ASKER CERTIFIED SOLUTION

dhenson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dhenson

If your up to using the Execute sql task instead... try this:

declare @zipfilename varchar(255)
declare @sFilename varchar(255)
declare @command varchar(255)

set @sFilename = '\\172.168.1.86\K$\myfile' + convert(varchar(50),getdate(),112) + '.bak'
set @zipfilename = 'c:\OGL_cta_backup.zip'

set @command = 'c:\progra~1\winzip\winzip32 -min -e ' + @zipfilename + ' ' + @sFilename
exec master..xp_cmdshell @command

If my syntax is correct, the above should unzip 'c:\OGL_cta_backup.zip' to a file named:
'\\172.168.1.86\K$\myfile20040324.bak'

sw7104

ASKER

This has been so frustrating. Note that I've switched to local machine and files. Also, I've tried to strip this to the bare essentials.

This command succeeds at command line, in under 1 second total run-time (file is 409 KB uncompressed):

c:\Progra~1\WinZip\WINZIP32.EXE -min -a -en "c:\OGL_cta_backup.zip" "C:\Documents and Settings\SWatkins\My Documents\1Projects\OglebayNorton\test20040324.bak"

Yet, when I run the following in Query Analyzer, the process just grinds away and hangs and hangs. I'm at 8 minutes plus, and still "processing".

Everything is on the local machine/drive, a 1 gig mem XP Professional box.

Thanks for all your help. I'm about to put my fist through my machine.

--------------------------------------------------------------------------------
declare @localfilename varchar(255)
declare @zipfilename varchar(255)
declare @command varchar(255)
set nocount on
set @localfilename = '"C:\Documents and Settings\SWatkins\My Documents\1Projects\OglebayNorton\test20040324.bak"'
set @zipfilename = '"c:\OGL_cta_backup.zip"'
--C:\Progra~1\WinZip\WINZIP32.EXE -min -a -en "c:\OGL_cta_backup.zip" "C:\Documents and Settings\SWatkins\My Documents\1Projects\OglebayNorton\test20040324.bak"
set @command = 'C:\Progra~1\WinZip\WINZIP32.EXE -min -a -en ' + @zipfilename + ' ' + @localfilename
--SELECT @command
exec master..xp_cmdshell @command

sw7104

ASKER

Ultimately, I had to abandon the xp_cmdshell approach. I went back to adwiseman's approach, which I did not initially understand, but after testing and reviewing the Dynamic Properties Task, I figured it out.

Thanks to dhenson for the time and trouble -- I'm guessing the xp_cmdshell approach works, but I couldn't get it to.

My near-final solution (without the "date in filename" manipulation part) on my local dev machine:
-----------------------------
Function Main()

Set task = DTSGlobalVariables.Parent.Tasks("DTSTask_DTSCreateProcessTask_1").CustomTask
task.ProcessCommandLine = """C:\Program Files\WinZip\WINZIP32.EXE"" -min -a -en ""c:\OGL_cta_backup.zip"" ""C:\Documents and Settings\SWatkins\My Documents\1Projects\OglebayNorton\test20040324.bak"" "

Main = DTSTaskExecResult_Success

End Function
-----------------------------