Accessing OLEDB Connection Managers in a Script

Accessing ADO.Net Connection Managers from an SSIS script task / script component is pretty easy – you just need to cast the object returned from AcquireConnection() to the appropriate class (i.e. SqlConnection if you’re using SQL Native Client).

If you can’t use ADO.Net for some reason, and are using OLEDB connection managers, it’s a little trickier. Since the AcquireConnection() method of the OLEDB connection manager returns a native COM object, I didn’t think there was a way to make this work, but today someone showed me how to do it!

By casting the Connection Manager’s InnerObject to the IDTSConnectionManagerDatabaseParameters100 interface (IDTSxxx90 in 2005), you can call the GetConnectionForSchema() method to return an OleDbConnection object.

Note, you’ll need to add a reference to the Microsoft.SqlServer.DTSRuntimeWrap assembly to get the IDTSConnectionManagerDatabaseParameters100 interface. If you’re doing this in a script task, you’ll need to prefix the Microsoft.SqlServer.Dts.Runtime.Wrapper namespace (or use fully qualified names) so that it doesn’t conflict with the namespace for the VSTA proxy classes.

Keep in mind that there are a couple of limitations with this approach:

You won’t be able to enlist in the current transaction

This connection doesn’t honor the “retain same connection” setting

ADO.Net is still the recommended connection manager type for scripts, but I found this to be a nice work around.

Could you please explain what you did to access the DTSRuntimeWrap assembly? I’m trying to programmatically get information about an OLEDB provider that I used to connect to a SAS datasource, so ADO.NET is not an option for me.