Passing DTS Global Variables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Passing DTS Global Variables

Author

Topic

MikeL
Starting Member

3 Posts

Posted - 2005-05-17 : 13:57:31

I am trying to pass a DTS package global variable to an executable via DTS's Execute Process Task. There is an area to enter Parameters. The problem is the global variable that I enter does not get interpreted before it gets passed to the executable.

SreenivasBora
Posting Yak Master

164 Posts

Posted - 2005-05-17 : 18:44:08

Hi Mikel,

Try this way you will definately success.

I am creating a executable file from visual basic and calling this exe from DTS and passing parameter.

1. Open VB, new project then goto Project --> Project Properties and goto Make TAB.
2. Supply "Command line Arguments" ---> Command$. Then OK
3. Open a form and place a command button and write this piece of code
On Command Click() write below stuff....

Dim CmdArgs As String
CmdArgs = Command$
MsgBox CmdArgs
End

4. Then make a Executable on your desktop.
5. create a DTS package and choose "Execute process task" and select this executable file and pass parameters as "Test"
6. Then Execute DTS package, it will involke your executable file.
7. click on button it will show a message box with your parameter data.

With Regards
Sreenivas Reddy B

MikeL
Starting Member

3 Posts

Posted - 2005-05-18 : 08:28:19

Yes, passing a literal works fine, but I'm trying to pass the contents of a DTS global variable.

MikeL
Starting Member

3 Posts

Posted - 2005-05-18 : 08:58:00

Here is a description of the problem:
Created a global variable in DTS, lets say "tempvar," with the value, "12345."

Now I want to pass this global as a parameter, to an executable, via the Execute Process Task.

I have tried:
tempvar --> in the executable came across as the literal "tempvar"
&tempvar --> in the executable came across as the literal "&tempvar"
%tempvar% --> in the executable came across as the literal "%tempvar%"
DTSGlobalVariables("gdum").Value --> DTSGlobalVariables(gdum).Value
... nothing has worked

I also tried using an ActiveX script. I can see the contents of tempvar, but do not know how to call an executable from the script.

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2005-05-24 : 05:45:09

Add an ActiveX to initialise the ProcessCommandLine property of the CreateProcessTask with the value of your global variable prior to executing the related step.
Something like this:


Function Main()
	Dim objPkg
	Dim objCmd
	Dim strCmd
	Dim strParam

'Initialise local variable with path of batch file from global variable
	strCmd = DTSGlobalVariables("gv_cmd").Value
'Initialise local variable with parameter string
	strParam = DTSGlobalVariables("gv_param").Value

'Set package variable
	Set objPkg = DTSGlobalVariables.Parent
'Set createprocesstask variable
	Set objCmd = objPkg.Tasks("DTSTask_DTSCreateProcessTask_1")

'Add parameter string to command string
	strCmd = strCmd & " " & strParam

'Set command line property of create process task
	objCmd.Properties("ProcessCommandLine").Value = strCmd

'Destroy objects
	Set objCmd = Nothing
	Set objPkg = Nothing

'Return success
	Main = DTSTaskExecResult_Success
End Function

Create the global variables, then add this script (substituting in the name of your create process task if different).
Add a precedence constraint between this task and the create process task to ensure that this is run first.

Mark

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources