Working with Connection Managers Programmatically
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