dcsimg

DTS How to...dynamically change...Connections

December 5, 1999

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:\YourPath\YourFile.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.








    The Network for Technology Professionals

    Search:

    About Internet.com

    Legal Notices, Licensing, Permissions, Privacy Policy.
    Advertise | Newsletters | E-mail Offers