AcquireConnection returns null

Hi,

we are facing some issue to get the underlying OledbConnection from the runtime ConnectionManager.

below is the code sample that we are using

IDtsConnectionService conService = (IDtsConnectionService)this.serviceProvider.GetService(typeof(IDtsConnectionService));

if (conService == null)

return;

ArrayList conCollection = conService.GetConnectionsOfType("OLEDB");

for (int count = 0; count < conCollection.Count; count++)

{

string conName = ((ConnectionManager)conCollection[count]).Name;

if (conName == conMgrname)

{

conMgr = DtsConvert.ToConnectionManager90((ConnectionManager)conCollection[count]);

ConnectionManager cm = DtsConvert.ToConnectionManager(conMgr);

Microsoft.SqlServer.Dts.Runtime.Wrapper.ConnectionManagerAdoNet cmado = cm.InnerObject as Microsoft.SqlServer.Dts.Runtime.Wrapper.ConnectionManagerAdoNet;

OleDbConnection conn= cmado.AcquireConnection(null) as OleDbConnection;

}

}

In the sample above the conn is null.

To this query Darren replied to use the connection of type ADO.NET:System.Data.OleDb.OleDbConnection, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089 to create the connection.

Your call to GetConnectionsOfType("OLEDB"); will return native OLE-DB connections, not ADO.NET connections, using the OleDbConnection.

The connection type for the ADo>NET OLE-DB connection is -

ADO.NET:System.Data.OleDb.OleDbConnection, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089

but if user creates an New OLEDB Connection from the Connection Manager panel of the BIDS and selects the same from the custom UI how to get the underlying OLEDBConnection? the CreationName in this case is "OLEDB" and not ADO.NET:System.Data.OleDb.OleDbConnection, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089.

Replace the code inside the if branch with this:

ConnectionManager cm = conCollection[count] as ConnectionManager;

OleDbConnection conn= cm.AcquireConnection(null) as OleDbConnection;

HTH.

|||

Hi bob,

It still returns me null.

Thanks,

Dharmbir

|||

You are right. OLE DB connection manager uses native connections internally and the AcquireConnection method does not know how to cast it to managed OleDbConnection.

You may try if the following will give you the managed connection:

oleDbConnection = ((IDTSConnectionManagerDatabaseParameters90)connectionManager.InnerObject).GetConnectionForSchema() as OleDbConnection;

However, I am not confident you can use it in the execution time.

HTH.

|||

Hi Bob,

thanks a lot. it really worked fine.

now i have one more issue regarding the connection informaction. I am using OracleConnection at runtime and for this reason i want to fetch the user id, password and datasource information from the connectionstring of OledbConnection. but the connection string doesn't exposes the password.

Can you please help me out for this?

Thanks,

Dharmbir

|||There is no way to get a password from a connection manager because it is a sensitive information. I would go with limiting your component to only use ADO.NET OLE DB connections (the one you can get using AcquireConnection) or provide a way for the users to enter missing passwords.|||

Hi All!

I have a problem with AcquireConnection in c#... I wrote this code:

public override void AcquireConnections(object transaction)

{

if (ComponentMetaData.RuntimeConnectionCollection[0].ConnectionManager != null)

{

ConnectionManager cm = DtsConvert.ToConnectionManager(ComponentMetaData.RuntimeConnectionCollection[0].ConnectionManager);

ConnectionManagerAdoNet cmAdo = cm.InnerObject as ConnectionManagerAdoNet;

if (cmAdo == null)

throw new Exception("The ConnectionManager " + cm.Name + " is not an ADO connection.");

this.conn = cmAdo.AcquireConnection(transaction) as OracleConnection;

}

but the 'conn' is ALWAYS null...

I try

this.conn = ((IDTSConnectionManagerDatabaseParameters90)cmAdo).GetConnectionForSchema() as OracleConnection;

too, but no result: the 'conn' is null again...

Nobody help me?

Martina