DTS How to…dynamically change…Connections

The Name is what you call it when you first create the connection. The Name is unique to that
connection and therefore can be used to identify it within the package connections collection.
ProviderID specifies the underlying OLE DB provider, and you may have multiple connections
of the same type. The common ProviderID values are listed below:

If your data source is not listed above then create and save a sample package using that provider.
Then use ScriptPkg
and examine the output.

N.B. All of the examples below are written in ActiveX Script (VBScript),
and are designed for use within an Active Script Task. They will of course
function equally well in Visual basic with a few minor tweaks.

The following example examines a package, and changes all of the SQL Server connections
to use a server called “MyServer”, and a database called “MyDB”:

Option Explicit
Function Main()
    ' Declare Variables
    Dim oPKG
    Dim oConnection
    ' Get Package Object
    Set oPKG = DTSGlobalVariables.Parent
    ' Examine Connections
    For Each oConnection in oPKG.Connections
        'Test for SQL Server Connection
        If oConnection.ProviderID = "SQLOLEDB.1" Then
            'Set new Server (DataSource) value
            oConnection.DataSource = "MyServer"
            'Set new Database (Catalog) value
            oConnection.Catalog = "MyDB"
        End If
    Next
    ' Clear Up
    Set oConnection = Nothing
    Set oPKG = Nothing

    Main = DTSTaskExecResult_Success
End Function

If you are only interested in a specific Connection you can reference it directly by name.
This simple example changes the file name property (DataSource) for a named Text File Connection.

Option Explicit
Function Main()
    ' Declare Variables
    Dim oPKG
    Dim oConnection
    ' Get Package Object
    Set oPKG = DTSGlobalVariables.Parent
    ' Get Named Connection
    Set oConnection = oPKG.Connections("Text File (Source)")
    ' Set Filename to new value
    oConnection.DataSource = "X:YourPathYourFile.txt"
    Set oConnection = Nothing
    Set oPKG = Nothing

    Main = DTSTaskExecResult_Success
End Function

Notice that in this case DataSource is not the server as it was
for the SQLOLEDB.1 provider, but the file name.

All connections have certain base properties as defined in SQL Server
Books Online, Connection Object (DTS).
To access the provider specific properties you need to delve into the ConnectionProperties,
also known as the OLEDBProperties collection.
This example changes the OLEDBProperty for a DTSFlatFile.1 provider column delimiter:

Option Explicit
Function Main()
    ' Declare Variables
    Dim oPKG
    Dim oConnection
    Dim oOLEP
    ' Get Package Object
    Set oPKG = DTSGlobalVariables.Parent
    'Get Named Connection
    Set oConnection = oPKG.Connections("Text File (Source)")
    ' Get the OLEDBProperty ("Column Delimiter")
    '   N.B. Column Delimiter only valid for Delimited Files
    '        "File Format" = 1
    Set oOLEP = oConnection.ConnectionProperties("Column Delimiter")
    ' Set the Property Value
    oOLEP.Value = "|"
     ' Clear Up
    Set oOLEP = Nothing
    Set oConnection = Nothing
    Set oPKG = Nothing

    Main = DTSTaskExecResult_Success
End Function

Unfortunately these OLEDB Properties are not defined in SQL Server Books Online. One can assume that this is
because they may change in future releases, but in the meantime they are very important in
designing a truly flexible package. To find out the specific properties you can use
ScriptPkg or a small utility I’ve written,
DTSObject

You will often want to set properties to values held elsewhere, my
Text File Connection file name article demonstrates methods
for GlobalVariables (DTS) and values held in a SQL table.