Accessing OLEDB Connection Managers in a Script

Accessing OLEDB Connection Managers in a Script

Rate This
  • Comments 13

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).

SqlConnection conn = (SqlConnection)Dts.Connections["adonet"].AcquireConnection(null);

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.

2008 (C#):

ConnectionManager cm = Dts.Connections["oledb"];
IDTSConnectionManagerDatabaseParameters100 cmParams = cm.InnerObject as IDTSConnectionManagerDatabaseParameters100;
OleDbConnection conn = cmParams.GetConnectionForSchema() as OleDbConnection;

2005 (VB):

Dim cm As ConnectionManager
Dim cmParam As Wrapper.IDTSConnectionManagerDatabaseParameters90
Dim conn As OleDb.OleDbConnection

cm = Dts.Connections("oledb")
cmParam = CType(cm.InnerObject, Wrapper.IDTSConnectionManagerDatabaseParameters90)
conn = CType(cmParam.GetConnectionForSchema(), OleDb.OleDbConnection)

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:

  1. You won’t be able to enlist in the current transaction
  2. 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.

Leave a Comment
  • Please add 2 and 3 and type the answer here:
  • Post
  • 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.

  • In 2008, you should be able to simply add it as an assembly reference for your project (DTSRuntimeWrap can be found in the GAC).

    In 2005, you'll need to add the assembly to the .NET framework directory. There is a good writeup on how to do this on sqlservercentral.com.

    http://www.sqlservercentral.com/articles/SQL+Server+2005+-+SSIS/3180/

  • Wow, I didn't know that connection strings of OleDB Connection Managers can be retrieved from a script task until today. This changes everything. Hehe. Thanks a lot for this post!!!

  • Hey Matt Masson ,

    I am getting following error...

     ConnectionManager cm = Dts.Connections["oledb"];

    error:The Type of namespace name could not be found???

    as well as The name "DTS" does not exist in current context

    Please Help

  • The code above works with a Script Task. For a script component, you'll need to configure your connection managers on the editor page.

    See: msdn.microsoft.com/.../aa337080.aspx

  • Hi Matt, may I know how to call a stored procedure inside the script task with your OLEDB connection manager as shown in the example above? Thanks!

  • It's alright, I've discovered how to call a sp inside th script task, thanks for this great article though, it helps me alot!

  • Does NOT work!!!! try it in ssis 2012

  • It should continue to work in 2012. What errors are you hitting?

  • Can not convert to SqlConnection.  ConnMgr returns NULL no matter how it is cast.

    Here is my code, same I think as yours.

    IDTSConnectionManager100 ConnMgr;

       object transistion;

       SqlConnection connection;

       List<LinkData> links;

       public override void AcquireConnections(object Transaction)

       {

           ConnMgr = this.Connections.Connection;

           transistion = this.Connections.Connection.AcquireConnection(null);

           connection = transistion as SqlConnection;

       }

  • Hi ,

    In the above code I am not able to get Innerobject prorperty , Am i missing something.

  • Same question as Harsha.

    I can't find the way to get the InnerObject property inside a Script Component

  • When you're trying to do this in a Script Component:

    1. Access the ConnectionManager property (technet.microsoft.com/.../microsoft.sqlserver.dts.pipeline.wrapper.idtsruntimeconnection100.connectionmanager.aspx) to get the IDTSConnectionManager100 interface (technet.microsoft.com/.../microsoft.sqlserver.dts.runtime.wrapper.idtsconnectionmanager100.aspx).

    2. Use DtsConvert.GetWrapper() to get the managed ConnectionManager object

    technet.microsoft.com/.../cc284875.aspx

    3. Access the InnerObject like you do in the ScriptTask code sample

    technet.microsoft.com/.../microsoft.sqlserver.dts.runtime.connectionmanager.innerobject.aspx

Page 1 of 1 (13 items)