TechNet
Export (0) Print
Expand All

Working with Connection Managers Programmatically

 

Applies To: SQL Server 2016 Preview

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.

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 TypeConnection Manager Name
ADOADO Connection Manager
MSOLAP90Analysis Services Connection Manager
EXCELExcel Connection Manager
FTPFTP Connection Manager
HTTPHTTP Connection Manager
ODBCODBC Connection Manager
OLEDBOLE 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.

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 TypeConnection Manager NameType of Return ValueAdditional Information
ADO.NETADO.NET Connection ManagerSystem.Data.SqlClient.SqlConnection
FILEFile Connection ManagerSystem.StringPath to the file.
FLATFILEFlat File Connection ManagerSystem.StringPath to the file.
MSMQMSMQ Connection ManagerSystem.Messaging.MessageQueue
MULTIFILEMultiple Files Connection ManagerSystem.StringPath to one of the files.
MULTIFLATFILEMultiple Flat Files Connection ManagerSystem.StringPath to one of the files.
SMOServerSMO Connection ManagerMicrosoft.SqlServer.Management.Smo.Server
SMTPSMTP Connection ManagerSystem.StringFor example: SmtpServer=<server name>;UseWindowsAuthentication=True;EnableSsl=False;
WMIWMI Connection ManagerSystem.Management.ManagementScope
SQLMOBILESQL Server Compact Connection ManagerSystem.Data.SqlServerCe.SqlCeConnection

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

Community Additions

ADD
Show:
© 2016 Microsoft