Error with Transactions on Containers...

[Goodie666]

Hi there,

I am desining a simle ETL package that has a main sequence container for which I set the Transaction Attribute to "Required". In this container, I have a data flow task followed by an EXECUTE SQL task and based on the results of that query, a simple ForEach Loop container (ie inside the sequence container there is a foreach container) that calls a stored procedure with the results from my SQL task so that I can call the stored procedure with proper parameters after all rows have been updated.

When the transaction attribute is on "Required" I systematically get an error on my ForEach container that says: "Error: 0xC00291EC at Update Path from Parents, Execute SQL Task: Failed to acquire connection ".NET - DataBase". Connection may not be configured correctly or you may not have the right permissions on this connection."

However, If I set it to "Supported", it works fine... I'm using Vista 32bits and SQL 2005/SSIS SP3.

If anybody knows how to get around this issue it would be greatly appreciated. It seems to be MSDTC related but since I'm not running WinServer I cannot configure my MSDTC beyong simply using the local one...

Thanks,

Greg

 

[mrdenny]

What happens if you set it to required and run the package on a server class OS with MSDTC setup?

If you are going to have enforced transactions against a remote server it's going to be a MSDTC issue.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2008 Implementation and Maintenance / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Admin (SQL 2005/2008) / Database Dev (SQL 2005)

My Blog

 

[Goodie666]

Hi mrdenny,

Well that was kinda my next step. But I don't want to have to do that neither mind you. I program COM+ components on a daily basis using ADO connections and transactions and have never had this issue. I tried to change my connection from ADO.NET to ADO and it is actually working now...

I've tried to look at my msdtc log but nothing was there, it's really a strange issue that is hard to debug. In the SQL Profiler I didn't see anything, only that at some point, the code in my onerror handler would be called. The error would always be at the same time in my flow though, down to the record, like after X times, it would just get overloaded or something...

Greg

 

[mrdenny]

Very strange. I'm not up to speed on the differences between ADO.NET and ADO, so I'm not sure why one would fail and the other succeed.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2008 Implementation and Maintenance / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Admin (SQL 2005/2008) / Database Dev (SQL 2005)

My Blog

 

[Goodie666]

Me neither, and the strangest of all is that I program COM+ dlls on a daily basis and I've been using my local DTC without any issues since I'm using my local SQL Server. Remote clients require using the remote server's DTC but for development purposes, it's convenient to have everything locally.

It is indeed very strange but after 7 hours trying to debug this, I really don't know what else I could have tried .

Greg

 

[Goodie666]

Just to mention... I've tried a lot of different things, from having no code in my original SP to using a data flow task instead etc. but nothing would do

 

[MDXer]

This link should explain things a bit better for you.
Transactions in SQL Server 2005 Integration Services The articla is written by Jamie Thomson so it is extremely reliable. In it he notes that MSDTC must be running on the same server that SSIS is running on.

You may have written a lot of COM+ stuff, but remember SSIS comes with it's own set of rules and requirements so things are bound to differ at some point.

 

[Goodie666]

Hi MDXer,

Unfortunately I've already come across this article but have not found it very useful for my issue. I already know pretty well the different transaction modes (COM+ programming relies on the MsDTC just like SSIS for transaction handling).

As for running the msdtc on the same machine as SSIS, I'm running everything locally so it should not be a problem (at that point permissions for the DTC should not be an issue neither).

The issue here is that my transaction just aborts for no reason with no message except that failure to acquire a connection. I'm not too upset about having an error, my issue is in the fact that I haven't been able to find out any of the reasons why this was happening.

Greg