Issue with global variable

[SqlHunter]

Is there any way in DTS I can specify the connection in a global variable.So that when I move the DTS package from one environemnt to another I can just specify it through the global variable

 

[gradley]

You can certainly do this and then use the "Dynamic Properties Task" to assign all your connections to this value. The only problem I see is if you have multiple database connections, you need to somehow softcode the database name.

We actually started using a "Microsoft Data Link" connection checking the "Always read properties from UDL" box. That way each server environment can use the data link on that machine and you don't need to make any connection changes during install.

 

[SqlHunter]

How can I configure this.please help with this.In the dynamic task properties there are connections and all.how can I use it effectively for pumping data from source to destination where both the connection should be dynamically set

 

[gradley]

How do you plan on updating the Global Variables?

If you plan to do this when you move the package from one environment to the other, I would suggest you use an ActiveX script as your first step as follows:

Function Main()

DTSGlobalVariables("strDataSource").Value = "(local)"
DTSGlobalVariables("strUserID").Value = "user"
DTSGlobalVariables("strPassword").Value = "pwd"

Main = DTSTaskExecResult_Success
End Function

Next create a Dynamic properties task.

For the Data Source, double-click the DataSource of the connection. The Source = "Global Variable". Specify the strDataSource variable. Add the User ID and password the same way but at the OLE DB Properties level.

Good luck!