Connecting to Data Sources in the Script Task

Connection managers provide access to data sources that have been configured in the package. For more information, see Integration Services Connections and Connection Managers.

The Script task can access these connection managers through the Connections property of the Dts object. Each connection manager in the Connections collection stores information about how to connect to the underlying data source.

When you call the AcquireConnection method of a connection manager, the connection manager connects to the data source, if it is not already connected, and returns the appropriate connection or connection information for you to use in your Script task code.

Note

You must know the type of connection returned by the connection manager before calling AcquireConnection. Because the Script task has Option Strict enabled, you must cast the connection, which is returned as type Object, to the appropriate connection type before you can use it.

You can use the Contains method of the Connections collection returned by the Connections property to look for an existing connection before using the connection in your code.

Important

You cannot call the AcquireConnection method of connection managers that return unmanaged objects, such as the OLE DB connection manager and the Excel connection manager, in the managed code of a Script task. However, you can read the ConnectionString property of these connection managers, and connect to the data source directly in your code by using the connection string with an OledbConnection from the System.Data.OleDb namespace. If you must call the AcquireConnection method of a connection manager that returns an unmanaged object, use an ADO.NET connection manager. When you configure the ADO.NET connection manager to use an OLE DB provider, it connects by using the .NET Framework Data Provider for OLE DB. In this case, the AcquireConnection method returns a System.Data.OleDb.OleDbConnection instead of an unmanaged object. To configure an ADO.NET connection manager for use with an Excel data source, select the Microsoft OLE DB Provider for Jet, specify an Excel file, and enter Excel 8.0 (for Excel 97 and later) as the value of Extended Properties on the All page of the Connection Manager dialog box.

Connections Example

The following example demonstrates how to access connection managers from within the Script task. The sample assumes that you have created and configured an ADO.NET connection manager named Test ADO.NET Connection and a Flat File connection manager named Test Flat File Connection. Note that the ADO.NET connection manager returns a SqlConnection object that you can use immediately to connect to the data source. The Flat File connection manager, on the other hand, returns only a string that contains the path and file name. You must use methods from the System.IO namespace to open and work with the flat file.

Public Sub Main()

    Dim myADONETConnection As SqlClient.SqlConnection
    myADONETConnection = _
        DirectCast(Dts.Connections("Test ADO.NET Connection").AcquireConnection(Dts.Transaction), _
        SqlClient.SqlConnection)
    MsgBox(myADONETConnection.ConnectionString, _
        MsgBoxStyle.Information, "ADO.NET Connection")

    Dim myFlatFileConnection As String
    myFlatFileConnection = _
        DirectCast(Dts.Connections("Test Flat File Connection").AcquireConnection(Dts.Transaction), _
        String)
    MsgBox(myFlatFileConnection, MsgBoxStyle.Information, "Flat File Connection")

    Dts.TaskResult = Dts.Results.Success

End Sub

See Also

Other Resources

Integration Services Connections
Creating Connection Managers

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release	History
14 April 2006	Changed content: Improved explanation of limitations when using connection managers that return native objects.