Edit

Working with Connection Managers Programmatically

  • Article

Applies to: SQL Server SSIS Integration Runtime in Azure Data Factory

In Integration Services, the AcquireConnection method of the associated connection manager class is the method that you call most often when you are working with connection managers in managed code. When you write managed code, you have to call the AcquireConnection method to use the functionality of a connection manager. You have to call this method regardless of whether you are writing managed code in a Script task, Script component, custom object, or custom application.

To call the AcquireConnection method successfully, you have to know the answers to the following questions:

  • Which connection managers return a managed object from the AcquireConnection method?

    Many connection managers return unmanaged COM objects (System.__ComObject) and these objects cannot easily be used from managed code. The list of these connection managers includes the frequently used OLE DB connection manager.

  • For those connection managers that return a managed object, what objects do their AcquireConnection methods return?

    To cast the return value to the appropriate type, you have to know what type of object the AcquireConnection method returns. For example, the AcquireConnection method for the ADO.NET connection manager returns an open SqlConnection object when you use the SqlClient provider. However, the AcquireConnection method for the File connection manager just returns a string.

This topic answers these questions for the connection managers that are included with Integration Services.

Connection Managers That Do Not Return a Managed Object

The following table lists the connection managers that return a native COM object (System.__ComObject) from the AcquireConnection method. These unmanaged objects cannot easily be used from managed code.

Connection Manager Type Connection Manager Name
ADO ADO Connection Manager
MSOLAP90 Analysis Services Connection Manager
EXCEL Excel Connection Manager
FTP FTP Connection Manager
HTTP HTTP Connection Manager
ODBC ODBC Connection Manager
OLEDB OLE DB Connection Manager

Typically, you can use an ADO.NET connection manager from managed code to connect to an ADO, Excel, ODBC, or OLE DB data source.

Return Values from the AcquireConnection Method

The following table lists the connection managers that return a managed object from the AcquireConnection method. These managed objects can easily be used from managed code.

Connection Manager Type Connection Manager Name Type of Return Value Additional Information
ADO.NET ADO.NET Connection Manager System.Data.SqlClient.SqlConnection
FILE File Connection Manager System.String Path to the file.
FLATFILE Flat File Connection Manager System.String Path to the file.
MSMQ MSMQ Connection Manager System.Messaging.MessageQueue
MULTIFILE Multiple Files Connection Manager System.String Path to one of the files.
MULTIFLATFILE Multiple Flat Files Connection Manager System.String Path to one of the files.
SMOServer SMO Connection Manager Microsoft.SqlServer.Management.Smo.Server
SMTP SMTP Connection Manager System.String For example: SmtpServer=<server name>;UseWindowsAuthentication=True;EnableSsl=False;
WMI WMI Connection Manager System.Management.ManagementScope
SQLMOBILE SQL Server Compact Connection Manager System.Data.SqlServerCe.SqlCeConnection

See Also

Connecting to Data Sources in the Script Task
Connecting to Data Sources in the Script Component
Connecting to Data Sources in a Custom Task