DTS Parameters 1

[AppSpecialist]

How do I use the value stored in a global variable as a parameter on a DTS Transform Data Task Query. The DTS source query is accessing a table via an ODBC connection. Each time I try to press the parameter button on the DTS Transform task... an error occurs.

I have no problem creating the global variable and assigning a value to it via an Execute SQL task.

In a nutshell... I am trying to use a value obtained via a query against my SQL Server connection as a parameter to pass to my ODBC connection on a DTS transform Data task?

Help?

 

[JamesLean]

You should be able to change it using a Dynamic Properties task. --James

 

[AppSpecialist]

Any suggestions on how to do that? I guess I need to change the base query used by the DTS Source Connection? But I don't know where to start in order to do that?

Thanks for the suggestion.

 

[JamesLean]

OK, so you've got your connection(s) set up and an execute SQL task which is populating a global variable?

Add a Dynamic Properties task which executes after the Exec SQL task. On the properties dialog for this task, click Add and you'll get a tree with all your objects and tasks which you can drop down and see what you can change about them.

Select the connection you want to change and select the property of that from the list on the right-hand side. Then click Set.

From the Source dropdown select Global Variable and then choose the name of the variable from the next list.

I hope this is roughly what you're trying to do! --James

 

[AppSpecialist]

Yes, after your initial email I gave this a try and it does the job perfectly... An yes I did pretty well exactly what you speced in your second email...

Thanks