Geek asks and answers

Can I use OleDbConnections with the Script component?

I am creating a ssis package and I want to use the 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: Cannot pass a COM object of type" System .__ ComObject "to a class type of System.Data.OleDb.OleDbConnection. Instances of types that represent COM components, cannot be attributed to types that do not represent COM components, however, they can be passed to interfaces if 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.

+5
3

, .

:

" AcquireConnection , , ADO.NET. ADO.NET OLE DB, .NET Framework OLE DB. AcquireConnection System.Data.OleDb.OleDbConnection . ADO.NET Excel, Microsoft OLE DB Jet, Excel Excel 8.0 ( Excel 97 ) " " " ".

!

+3

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.

ADO.NET, Aquire Connection

OLEDB, Microsoft.SqlServer.DTSRuntimeWrap

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

MSDN

+8

Just in case, someone was looking for this and could not find a real solution, you need to override the AcquireConnections, PreExceute and ReleaseConnections methods to use OleDbConnection. Trick - 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);
}

NTN

+3
source

More articles:


All Articles