Is it possible to use OleDbConnections with the Script Component?

Question

I'm building an ssis package and I wish to use an existing OleDbConnection inside the Script Component. Here is my code:

public override void AcquireConnections(object Transaction)
{
    base.AcquireConnections(Transaction);
    cm = this.Connections.Connection;
    con = (OleDbConnection)cm.AcquireConnection(Transaction);
    MessageBox.Show(con.ToString());
    
}

When I close BIDS, i get the following message: "System.InvalidCastException: Unable to cast COM object of type 'System.__ComObject' to class type 'System.Data.OleDb.OleDbConnection'. Instances of types that represent COM components cannot be cast to types that do not represent COM components; however they can be cast to interfaces as long as the underlying COM component supports QueryInterface calls for the IID of the interface."

The same code works fine with an Ado.Net connection. Can I use OleDbConnection here or Script Component only supports Ado.Net?

Supersaturate

This will help you: [link] (akshaya-m.blogspot.com/2017/02/…) – Francenefrances

@Francenefrances thanks for the suggestion. One downside, is that .ConnectionString approach only works if its using Windows Auth. This is because accessing ConnManager.ConnectionString like that always removes any password (for security). #30240073 – Downwards

Does this answer your question? How do I embed the Connection Manager of the Script Component in the C# code? – Symon

Answer 1

Thanks praveen.

I found the relevant part in your link:

"If you must call the AcquireConnection method of a connection manager that returns an unmanaged object, use an ADO.NET connection manager. When you configure the ADO.NET connection manager to use an OLE DB provider, it connects by using the .NET Framework Data Provider for OLE DB. In this case, the AcquireConnection method returns a System.Data.OleDb.OleDbConnection instead of an unmanaged object. To configure an ADO.NET connection manager for use with an Excel data source, select the Microsoft OLE DB Provider for Jet, specify an Excel file, and enter Excel 8.0 (for Excel 97 and later) as the value of Extended Properties on the All page of the Connection Manager dialog box."

Thanks!

Supersaturate

Helped me a lot. I changed my connection from OLEDB to ADO.NET and it worked fine. – Montage

Answer 2

As mentioned in the MSDN

You cannot call the AcquireConnection method of connection managers that return unmanaged objects, such as the OLE DB connection manager and the Excel connection manager, in the managed code of a Script task.

You need to use the ADO.NET connection manager if you want to use Aquire Connection method:

In order to use OLEDB connection add a reference to Microsoft.SqlServer.DTSRuntimeWrap and try the below code

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

MSDN Link

Viral

There is not such Dts property in Script Component, it's an Script Task property. However I found an answer in the link you provided, thanks. – Supersaturate

@Viral - the correct link is techcommunity.microsoft.com/t5/SQL-Server-Integration-Services/… -- yours doesn't work anymore. Also... My Script Task doesn't know about any Microsoft.SqlServer.DTSRuntimeWrap in my using. It doesn't compile. Where is that from? – Downwards

How can I add a reference to Microsoft.SqlServer.DTSRuntimeWrap from an SSIS Script Task? says that "you cannot add references to your script task in SSIS." Perhaps, you cannot do it for script components either? – Symon

Answer 3

Just in case someone googled this and couldn't find a real solution, you have to override the AcquireConnections, PreExceute and ReleaseConnections methods in order to use an OleDbConnection. The trick is the ConnectionString property:

OleDbConnection con;
OleDbCommand cmd;
IDTSConnectionManager100 connMgr;

/*Here you prepare the connection*/
public override void AcquireConnections(object Transaction)
{
    base.AcquireConnections(Transaction);
    connMgr = this.Connections.YourConnName;
    con = new OleDbConnection(connMgr.ConnectionString);
}

/*Here you prepare the sql command and open the connection*/
public override void PreExecute()
{
    base.PreExecute();
    cmd = new OleDbCommand("Some Select", con);
    cmd.CommandType = CommandType.Text;
    con.Open();
}

/*Here you execute your query for each input row*/
public override void Entrada0_ProcessInputRow(Entrada0Buffer Row)
{
    OleDbDataReader dr = cmd.ExecuteReader();

    while (dr.Read())
    {
       /*Do your stuff*/   
    }
}

/*And here you release the connection*/
public override void ReleaseConnections()
{
    base.ReleaseConnections();
    connMgr.ReleaseConnection(con);
}

HTH

Piet

I don't think this can work with connections without integrated security.. As far as I remember, the connMgr.ConnectionString doesn't hold the connections password. – Supersaturate

@Supersaturate yes that's what I've seen too. ConnectionString from a ConnectionManager loses the password. So it would only work for Windows Auth. – Downwards

@DonCheadle which should be the default and better choice anyway, so that is not a caveat of this answer. – Symon