You can change the Access MDB file of a connection at run-time using an
ActiveX Script Task. The following example reads the new filename from
a global variable and updates the connection called "Accounts".
' Pkg 201
Option Explicit
Function Main()
Dim oConn
Set oConn = DTSGlobalVariables.Parent.Connections("Accounts")
oConn.DataSource = DTSGlobalVariables("MDB Filename").Value
Set oConn = Nothing
Main = DTSTaskExecResult_Success
End Function
If you are using SQL Server 2000 then it is usually quicker and simpler to use Dynamic Properties Task.