AcquireConnection(txn) ?

In a script task I'm calling AcquireConnection(txn as Object) on a connection manager.

What is the txn parameter supposed to do or be used for?

I'm just passing an empty variable of type Object to this parameter and the method call is working fine...but I'm interested in knowing what its there for.

Thanks
JamieIt is the transaction context you want to use for any work done in that connection. Think of DTC, you need a context that can be supplied to all connections that make up that transacction so all work is marshalled together, well this is that handle.

Passing null will pretty much always work. If you have a transaction context then pass it otherwise don't worry.|||

DarrenSQLIS wrote:

It is the transaction context you want to use for any work done in that connection. Think of DTC, you need a context that can be supplied to all connections that make up that transacction so all work is marshalled together, well this is that handle.

Passing null will pretty much always work. If you have a transaction context then pass it otherwise don't worry.

Cool, that makes sense. I figured it was something I didn't have to worry about.

cheers Darren.

-Jamie|||Darren:
A follow-up on this: Ive been writitng a custom dataflow source that analyzes flatfiles by various properties (filesize, width etc.). The component works well when i have a stable flatfile connection. But when i do a for-each file in folder enumeration i cant get the updated connection information (ie filepath) from my component, it will always run against the first path passed by the connection manager. Any ideas on this?|||I've had issues with class level variables and loops since the class is only created once, that was a task though. Simple answer is I don't know, and don't have a suitable component to test with easily, but if you debug the component it should be very simple to work out what is going on. Put a break point on AcquireConnections for a start and see how many times it is called.

Another obvious test would be to add a script component, and make that consume the same connection, and just MsgBox out the connection string.

What do you get?|||

I have an SSIS package with its TransactionOption set to 'Required'. A data flow in the package has a script component that performs updates to a database table. The TransactionOption on the data flow is set to 'Supported'. However the script component does no get enlisted to the transaction because the updates made in the script component do not get rolled back when the package fails.

I'm thinking that I need to pass this 'transaction handle' to the AquireConnection method of the connection used by the script in order for the script to become part of the overall transaction.

In regard to your reply about transaction context. How do I determine it? I have no idea what value I should be passing. I am new to Sql Server beginning with Sql Server 2005. I have no background with DTC.

Thanks.

|||Please, one post will suffice. I have replied, and maybe even answered, in your new thread - http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1322436&SiteID=1

AcquireConnection(txn) ?

In a script task I'm calling AcquireConnection(txn as Object) on a connection manager.

What is the txn parameter supposed to do or be used for?

I'm just passing an empty variable of type Object to this parameter and the method call is working fine...but I'm interested in knowing what its there for.

Thanks
JamieIt is the transaction context you want to use for any work done in that connection. Think of DTC, you need a context that can be supplied to all connections that make up that transacction so all work is marshalled together, well this is that handle.

Passing null will pretty much always work. If you have a transaction context then pass it otherwise don't worry.|||

DarrenSQLIS wrote:

It is the transaction context you want to use for any work done in that connection. Think of DTC, you need a context that can be supplied to all connections that make up that transacction so all work is marshalled together, well this is that handle.

Passing null will pretty much always work. If you have a transaction context then pass it otherwise don't worry.

Cool, that makes sense. I figured it was something I didn't have to worry about.

cheers Darren.

-Jamie|||Darren:
A follow-up on this: Ive been writitng a custom dataflow source that analyzes flatfiles by various properties (filesize, width etc.). The component works well when i have a stable flatfile connection. But when i do a for-each file in folder enumeration i cant get the updated connection information (ie filepath) from my component, it will always run against the first path passed by the connection manager. Any ideas on this?
|||I've had issues with class level variables and loops since the class is only created once, that was a task though. Simple answer is I don't know, and don't have a suitable component to test with easily, but if you debug the component it should be very simple to work out what is going on. Put a break point on AcquireConnections for a start and see how many times it is called.

Another obvious test would be to add a script component, and make that consume the same connection, and just MsgBox out the connection string.

What do you get?|||

I have an SSIS package with its TransactionOption set to 'Required'. A data flow in the package has a script component that performs updates to a database table. The TransactionOption on the data flow is set to 'Supported'. However the script component does no get enlisted to the transaction because the updates made in the script component do not get rolled back when the package fails.

I'm thinking that I need to pass this 'transaction handle' to the AquireConnection method of the connection used by the script in order for the script to become part of the overall transaction.

In regard to your reply about transaction context. How do I determine it? I have no idea what value I should be passing. I am new to Sql Server beginning with Sql Server 2005. I have no background with DTC.

Thanks.

|||Please, one post will suffice. I have replied, and maybe even answered, in your new thread - http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1322436&SiteID=1

AcquireConnection returns null value

I have the following code in my custom source component's AcquireConnection method -

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

{

ConnectionManager cm = Microsoft.SqlServer.Dts.Runtime.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.NET connection.");

// Get the underlying connection object.
this.oledbConnection = cmado.AcquireConnection(transaction) as OleDbConnection;

if (oledbConnection == null)

throw new Exception("The ConnectionManager is not an ADO.NET connection.");

isConnected = true;

}

The value of oledbConnection is null.

I am trying to invoke the above method within a package that I am trying to create dynamically. Any help ?

private const string ADONETConnectionString = "Provider=SQLOLEDB.1;Data Source={0};Initial Catalog={1};Integrated Security=True;";

ConnectionManager PubTRCM = package.Connections.Add("ADO.NET:OLEDB");

PubTRCM.Name = "PubTR";

PubTRCM.ConnectionString = String.Format(ADONETConnectionString, paramPubServer, paramPubTRDB);

#region Create OLEDB Source Component

//Create a OLEDB Source Component
IDTSComponentMetaData90 Source = dataFlow.ComponentMetaDataCollection.New();
Source.Name = "OLEDBSource";

Source.ComponentClassID = typeof(Microsoft.Samples.SqlServer.Dts.AdoSourceSample).AssemblyQualifiedName;

//Get the design time instance of the source
CManagedComponentWrapper SourceDesignTime = Source.Instantiate();

//Initialize the component
SourceDesignTime.ProvideComponentProperties();

if (Source.RuntimeConnectionCollection.Count > 0)
{
Source.RuntimeConnectionCollection[0].ConnectionManagerID = PubTRCM.ID;
Source.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(PubTRCM);
}

SourceDesignTime.SetComponentProperty("SqlStatement", "Select PlantId from PlantDim");

//Reinitialize the metadata
SourceDesignTime.AcquireConnections(null);
SourceDesignTime.ReinitializeMetaData();
SourceDesignTime.ReleaseConnections();

#endregion

So what stage does the problem start? Is cmado an object? If so what type? What is the result from cmado.AcquireConnection, never mind the cast?

Have you tried debugging your component whilst it is being called like this?

Does the component work in the designer, I assume not.

|||

I could resolve this problem by changing the connection string from -

"Provider=SQLOLEDB.1;Data Source={0};Initial Catalog={1};Integrated Security=True;";

to

"Provider=SQLOLEDB.1;Data Source={0};Initial Catalog={1};Integrated Security=SSPI;";

and adding the connection manager to the package as follows -

ConnectionManager CMgr = package.Connections.Add("ADO.NETystem.Data.OleDb.OleDbConnection, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089");

Thanks,

Reni

AcquireConnection returns null value

I have the following code in my custom source component's AcquireConnection method -

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

{

ConnectionManager cm = Microsoft.SqlServer.Dts.Runtime.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.NET connection.");

// Get the underlying connection object.
this.oledbConnection = cmado.AcquireConnection(transaction) as OleDbConnection;

if (oledbConnection == null)

throw new Exception("The ConnectionManager is not an ADO.NET connection.");

isConnected = true;

}

The value of oledbConnection is null.

I am trying to invoke the above method within a package that I am trying to create dynamically. Any help ?

private const string ADONETConnectionString = "Provider=SQLOLEDB.1;Data Source={0};Initial Catalog={1};Integrated Security=True;";

ConnectionManager PubTRCM = package.Connections.Add("ADO.NET:OLEDB");

PubTRCM.Name = "PubTR";

PubTRCM.ConnectionString = String.Format(ADONETConnectionString, paramPubServer, paramPubTRDB);

#region Create OLEDB Source Component

//Create a OLEDB Source Component
IDTSComponentMetaData90 Source = dataFlow.ComponentMetaDataCollection.New();
Source.Name = "OLEDBSource";

Source.ComponentClassID = typeof(Microsoft.Samples.SqlServer.Dts.AdoSourceSample).AssemblyQualifiedName;

//Get the design time instance of the source
CManagedComponentWrapper SourceDesignTime = Source.Instantiate();

//Initialize the component
SourceDesignTime.ProvideComponentProperties();

if (Source.RuntimeConnectionCollection.Count > 0)
{
Source.RuntimeConnectionCollection[0].ConnectionManagerID = PubTRCM.ID;
Source.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(PubTRCM);
}

SourceDesignTime.SetComponentProperty("SqlStatement", "Select PlantId from PlantDim");

//Reinitialize the metadata
SourceDesignTime.AcquireConnections(null);
SourceDesignTime.ReinitializeMetaData();
SourceDesignTime.ReleaseConnections();

#endregion

So what stage does the problem start? Is cmado an object? If so what type? What is the result from cmado.AcquireConnection, never mind the cast?

Have you tried debugging your component whilst it is being called like this?

Does the component work in the designer, I assume not.

|||

I could resolve this problem by changing the connection string from -

"Provider=SQLOLEDB.1;Data Source={0};Initial Catalog={1};Integrated Security=True;";

to

"Provider=SQLOLEDB.1;Data Source={0};Initial Catalog={1};Integrated Security=SSPI;";

and adding the connection manager to the package as follows -

ConnectionManager CMgr = package.Connections.Add("ADO.NETystem.Data.OleDb.OleDbConnection, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089");

Thanks,

Reni

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

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

AcquireConnection Error - Lookup Component

Hi,

Trying to get a particular SSIS is package working - while running the Package Installation wizard a package is reported with the following errors:

AcquireConnection method fails with error code 0xC0202009 Then Lookup fails validation and returns code 0xC020801C

The odd thing is the Lookup does not exist in the package, it did exist at one point but was deleted.

I have tried searching the XML and can't find any reference to the Lookup

Any suggestions appreciated...

Thanks,

John

Could you check if you are not somehow referencing the old package?|||

Any ideas how?

The steps I've tried are:

Delete all the packages in the server file system store

Check the local package for the Lookup (not found)

Rebuild the solution.

Double click the manifest to deploy on the server to a file store with the validate option turned on.

Validation warning above occurs.

If I open the package in notepad and search for the name of the lookup I don't find anything

|||

You may want to try a brute force here and search for all files with extension .dtsx on your machine. You could find some you do not know about (VS sometimes makes copies). If you do, see if any of them is the troublemaker.

HTH.