DTC - Transaction context in use by another session

Hi

I have a master package that executes a series of sub packages. The master package is run from a SQL Agent job. The packages are reading from and writing to two databases on the same instance of SQL Server 2005.

If I set the Execute Package Tasks for the sub packages without any precedence constraints between them and set TransactionOption = Required at the master package level (and supported from there downwards) I get the following errors.

The event log shows me:

The SSIS Runtime has failed to enlist the OLE DB connection in a distributed transaction with error 0x8004D00A "Unable to enlist in the transaction.".

Running a SQL Profiler trace shows me:

Error: 3910, Severity: 16, State: 2
Transaction context in use by another session.

This problem is well documented and seems to go back to DTS in SQL Server 7.0 . . . see http://support.microsoft.com/?scid=kb;en-us;279857&spid=2852&sid=150

I can get round it by setting precedence between the sub packages - making them run one at a time solves the problem. But then we don't get the performance benefits of running the packages concurrently. Does anyone have any other solutions.

TIA . . . Ed

Ed,

are using the RTM or SP1 version?

There was a fix in SP1 that might address your problem.

Thanks.

|||

Hi Bob

We tried SP1 but reverted because there were some issues with it and we couldn't afford the time to play around with it.

I can leave the packages running in series for now and try running them in parallel with SP1 installed when I have time to experiment.

Thanks . . . Ed

|||

I'm getting the same error messages in almost the same scenario, but I have SP1 applied. In my case, I have a sub-package that starts its own distributed transaction and it has multiple data flows that run concurrently within the same container component. Sometimes it runs through just fine. Other times, one or another of the data flows will die with this error. It's unpredictable which one or if it will fail at all. If I sequence the data flows, the problem goes away.

Is there an alternative solution besides the one in SP1? What was the one in SP1?

Thanks,

Joe

|||

Are you using a configured common connection between the two (or more) packages? As a guess, try to make the connection string slightly different for each by appending the ";Application Name=?" property with the package name where the ? is.

Under a slightly different scenario I was able to get the same thing happening and this corrected my problem. I was using a shared config file to configure the connections and when I added the Application Name part it went away.

|||

None of my packages are using configured connections. They each have their own connection manager defined within their package. I do intend to make them configured in the future, so I'll keep that suggestion in mind.

Also, I can get the error to occur when executing only one of the subpackages at a time. They each are starting their own transaction. It's the data flows that have problems running concurrently.