Using connection managers in managed code

SQL Server 2008 Books Online will have a long-needed topic that lists the return values from the AcquireConnection API method for each in-the-box connection manager. The developer needs to be able to cast the return value to the proper type, but these types are somewhat...unpredictable. While the Flat File Connection Manager returns a string, the ADO.NET Connection Manager with SqlClient returns an open SqlConnection.

I distracted myself at work one day by adding and configuring a connection manager of each type to a package, then writing some simple Script Task code that iterated over each connection, called AcquireConnection, checked and printed the type of the return value, and for strings, a sample of the string. Except for the delay of installing FTP, SMTP, and Message Queueing, this only took a few minutes. The output appears below.

Those connection managers that return a native object (System.__COMObject) cannot be used in managed code.

The forthcoming SAP BW connection manager returns a managed object, while the forthcoming Oracle and Teradata connection managers return native COM objects.

ADO
System.__ComObject

ADO.NET:System.Data.SqlClient.SqlConnection, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
System.Data.SqlClient.SqlConnection

MSOLAP90
System.__ComObject

EXCEL
System.__ComObject

FILE
System.String
   C:\Users\douglasl\Documents\0 Temporary\Work\TestSigningOutput.txt

FLATFILE
System.String
   C:\Users\douglasl\Documents\0 Temporary\Work\TestSigningOutput.txt

FTP
System.__ComObject

HTTP
System.__ComObject

MSMQ
System.Messaging.MessageQueue

MULTIFILE
System.String
   C:\Users\douglasl\Documents\0 Temporary\Work\commands for test certificate.txt

MULTIFLATFILE
System.String
   C:\Users\douglasl\Documents\0 Temporary\Work\TestSigningOutput.txt

ODBC
System.__ComObject

OLEDB
System.__ComObject

SMOServer
Microsoft.SqlServer.Management.Smo.Server

SMTP
System.String
   SmtpServer=smtphost;UseWindowsAuthentication=True;EnableSsl=False;

WMI
System.Management.ManagementScope

SQLMOBILE
System.Data.SqlServerCe.SqlCeConnection