DTS How to…dynamically change…Connections

3 How to dynamically change…Connections


I would recommend familiarising yourself with ScriptPkg before reading this article:

  • Dynamically change package properties utilising ScriptPkg


    This article will focus on changing Connection properties, for changing Task properties please see:

  • How to dynamically change…Tasks


    On with the article..


    Each package contains a collection of Connections, which not surprisingly contains Connection objects.
    Different connection objects use different Providers and have different ConnectionProperties.
    To alter a connection we first have to identify it within the package, for which there are two main properties of interest:

  • oConnection.Name
  • oConnection.ProviderID

  • 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:

    ProviderID Data Source
    SQLOLEDB.1 SQL Server
    Microsoft.Jet.OLEDB.4.0 Jet e.g. Access & Excel
    DTSFlatFile.1 Text Files


    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.

    Latest Articles