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