I am attempting to change the properties of a connection that is used by a
generic sql task. I have coded to change the Database/Server as shown below.
But the tasks that use this connection don't seem to recognize the change
and run against the initial Databaser/Server. Is there something missing
that forces sort of refresh within the object?

Function Main()
Dim oPkg
Dim oConn
Dim stpBeginMe

'Get package object
set oPkg = DTSGlobalVariables.Parent

'Get Connection object
set oConn = oPkg.connections("Variable Connection")

oConn.Catalog = DTSGlobalVariables("OMS_Name").Value
oConn.DataSource = DTSGlobalVariables("OMS_Server").Value

set oConn = nothing
set oPkg = nothing

Main =DTSTaskExecResult_Success
End Function

Make sure the Onject names in your Transform tasks are not 3 parters.
Change them to just

<owner>.<name>

Also make sure each task is set to "Close connection on completion" in the
workflow properties

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.konesans.com - Consultancy from the people who know


"whornak" <whornak (AT) discussions (DOT) microsoft.com> wrote