Passing a Global Variable to a Transform Data Task

[Lezza]

I'm a DBA, so bear with me, I only know T-SQL.

In DTS, I need to pump data from one machine to another based on a max(date) located in a table on the destination server.

I've looked up Global Variables and I'm this far:

1) Execute SQL Task with Select Max(date) etc. with an output parameter called o_max_date.
2) On success of #1) I have an ActiveX Script Task that uses the following code:

Function Main()
MsgBox "TheMaxPostingDate is " & DTSGlobalVariables("o_max_postingdate&quot.value

Main = DTSTaskExecResult_Success
End Function

I'm not sure what MsgBox means, but the rest is not too bad.

Now, how do I pass the max(date) from the activeX script into my Transform Data Task?

Lezza

 

[MissTipps]

In your transform data task, presumably you will need a WHERE clause to bring back data based on the parameter you just created. All you need to do is specify ? as the filter for your WHERE clause, e.g. ...WHERE <fieldname> >= ?.... then click the 'Parameters' button and ensure that the o_max_date parameter is displayed as the input parameter.

You are now passing the parameter you just created to the transform data task.

BTW I'm not sure what purpose the ActiveX script is serving in your DTS package, but it is surplus to requirements based on what I've just told you. All that the MsgBox command does, is literally provide a message box to the user, which in this case will inform them of the value of o_max_date.

Hope this helps.

Cheers

Clare

 

[Lezza]

Thank you!