It's 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?

Thanks in advance.

Answer 1

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

Answer 2

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