Solved

OLE DB Source not working with Distributed Transaction

Posted on 2008-10-30
17
8,191 Views
Last Modified: 2013-11-10
I've got an SSIS package that I've been trying to adapt to use distributed transactions. Whenever I set the TransactionOption to "Required" in the outermost container, everything stopped working. The only way I could get any SQL steps to work would be to change the TransactionOption to "NotSupported", which kind of defeats the purpose. :)

The error I get from my OLE DB Source is:

SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "MyConnection" failed with error code 0xC001A004.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.

The Connection Manager is using OLE DB and I have RetainSameConnection set to True.

There is a linked server involved and I do have DTC set up on both machines. I've tested the connection between them using DTCPing and all tests were successful.

Could anyone suggest things I could try? I suspect it's something to do with the settings of DTC.

See attached file for current DTC settings.

DTCSettings.png
0
Comment
Question by:Ken_Lyon
  • 10
  • 7
17 Comments
 

Author Comment

by:Ken_Lyon
ID: 22842513
Some further details about the two servers involved in the Distributed Transaction:

Server #1: Windows Server 2003 R2 - Standard Edition - SP2, SQL2005
Server #2: Windows Server 2003 - Standard Edition - SP1, SQL2000

0
 
LVL 17

Accepted Solution

by:
HoggZilla earned 350 total points
ID: 22842585
Can you confirm that the Microsoft Distributed Transaction Coordinator is running the same server / local machine you are executing the SSIS pacage from?
0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22842700
The first debug I suggest is to remove the "Linked Server" from your transaction. If this resolves the issue then you might consider using a Transformation component in the Data Flow task to bring in the other database. It is probably not a DTC issue, probably a relationship between the TransactionOption in SSIS and the use of DTC involving a linked server. My guess. The more information you can provide we can help debug.
0
 

Author Comment

by:Ken_Lyon
ID: 22842829
Hi HoggZilla, thanks for your quick reply!

MSDTC is running on both of the servers concerned with the SSIS package and distributed transaction. In terms of where I'm executing it from, I'm taking it you mean the one defined in the connection? I'm currently running the package through VS 2005 on my workstation. It's an XP Pro machine with SP3. The workstation does have MS DTC running, but Network DTC Access is disabled. Should I change those settings on the workstation?

Regarding removing the linked server from the transaction...  I've disabled most of the steps in the package, including the ones that refer to the linked server. Even a SQL Task running SELECT @@DBTS fails. It says:

[Execute SQL Task] Error: Failed to acquire connection "MyConnection". Connection may not be configured correctly or you may not have the right permissions on this connection.

[Connection manager "MyConnection"] Error: The SSIS Runtime has failed to enlist the OLE DB connection in a distributed transaction with error 0x8004D024 "The transaction manager has disabled its support for remote/network transactions.".

This last bit's interesting...

I'll try tweaking the settings of MS DTC on my workstation as that could be the answer.
0
 

Author Comment

by:Ken_Lyon
ID: 22842938
Ok, so I switched on MS DTC on my workstation and something interesting is happening now. It's still failing, but the errors are slightly different:

[Execute SQL Task] Error: Failed to acquire connection "MyConnection". Connection may not be configured correctly or you may not have the right permissions on this connection.

[Connection manager "MyConnection"] Error: The SSIS Runtime has failed to enlist the OLE DB connection in a distributed transaction with error 0x8004D00E "The transaction has already been implicitly or explicitly committed or aborted".

Could there be something in the OLE DB connection that I'm missing?


Connection-Properties.png
0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22843091
OK, I think we are getting somewhere. I do beleive you have to have MDTC running on your machine if you execute the SSIS Package from your machine, as you are. So with that change, this new connection manager issue. You mentioned you have retain connection set to True, have you tried changing that to False?
0
 

Author Comment

by:Ken_Lyon
ID: 22843106
I have tried it in both states and it fails. However I found something else...

Having established that the DTC settings on my own workstation definitely do play a part in all this, I tested the connection between it and one of the servers. The server performing a RPC test on my workstation failed:

Invoking RPC method on MyWorkstation
Problem:fail to invoke remote RPC method
Error(0x5) at dtcping.cpp @303
-->RPC pinging exception
-->5(Access is denied.)
RPC test failed

I noticed I've got Windows Firewall running on my workstation. I'm going to add an exception and see if that works.
0
 

Author Comment

by:Ken_Lyon
ID: 22843177
Ok, we're in business, kind of.

Having added MSDTC.exe to the exceptions list in Windows Firewall, the SSIS package now runs successfully. (I've tested a very basic one with a single task that does SELECT @@DBTS.)

The strange thing is that DtcPing is still failing with the same error. Should I be concerned about this?
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 17

Expert Comment

by:HoggZilla
ID: 22843209
Should I be concerned about this?
I don't know. If the TransactionOption is set to Required and all down stream objects set to Supported or Required, SSIS should be able to guarantee the process. But the ping failing is a concern to me. Just don't know.

0
 

Author Comment

by:Ken_Lyon
ID: 22843264
Hmm, ok. If everything else works, I might ignore that. After all, it's my workstation that's going to be executing stuff on the server, not the other way around.

There's something else that's come up now:

I'm now testing my original package, now that the simple case works. One of the steps seems to be trying to start another transaction. The error suggests it's because XACT_ABORT is set to OFF. Incidentally, this is the step that's writing to the linked server.

How would I switch XACT_ABORT to ON in this setup?

(Going home for the day now, so no rush for an answer in the next few minutes or anything.)
0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22843650
I will look into that, but just to verify. Every step in the package should have the TransactionOption set to "Supported". I think it can be required as well, but better to be safe. Only the Parent, the package itself should be set to "Required". I doubt that is the problem, but a good validation step at this point.
Do all of the connection managers have the property SupportsDTCTransactions set to True?
0
 

Author Comment

by:Ken_Lyon
ID: 22848553
I've got the container's TransactionOption set to "Required" and everything else has it set to "Supported". Everything has IsolationLevel of Serializable.

There are two Data Flow Tasks that fail. Both of them write data to a linked server. Here's the error they get:

[Data Flow Tasks 1 [2836]] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available.  Source: "Microsoft SQL Native Client"  Hresult: 0x80004005  Description: "Unable to start a nested transaction for OLE DB provider "SQLNCLI" for linked server "Server2". A nested transaction was required because the XACT_ABORT option was set to OFF.". An OLE DB record is available.  Source: "Microsoft SQL Native Client"  Hresult: 0x80004005  Description: "OLE DB provider "SQLNCLI" for linked server "Server2" returned message "Cannot start more transactions on this session.".".

0
 
LVL 17

Assisted Solution

by:HoggZilla
HoggZilla earned 350 total points
ID: 22848679
I know we already went through this, but is it possible to accomplish your task by connecting directly to the  "linked" SQL Server using a Connection Manager and remove the Linked Server references? I HATE linked servers, crap like this, I have not seen documented but I am not suprised.
0
 

Author Comment

by:Ken_Lyon
ID: 22848698
I can try it. I was doing it that way to begin with, before I tried to add distributed transactions. I had thought that for the distributed transaction to work I needed to reference the second server through the same Connection Manager, and consequently the need for Linked Servers.
0
 

Author Comment

by:Ken_Lyon
ID: 22848793
Cool, that's working! I've got two different connection managers for the different databases, which reside on different servers.

Now the whole process works correctly and I've tried putting in breakpoints, cancelling etc. and I'm seeing the correct behaviour of locking and rolling back where appropriate. I might tweak the IsolationLevel used, I'm not sure. I won't bother you with that, though. I think that's not as critical. The main thing is that it works. If it becomes a huge performance burden, I can look into that.

I'm mildly concerned about some performance hits we've encountered yesterday and today when I've been restarting DTC on one of the servers, but I think that will probably trail off. I've only put the settings back to what they were in the first place so I think it's the re-setting rather than the new values that affected performance.

Thanks so much for your help. I appreciated the speed of your response and your suggestions.
0
 

Author Closing Comment

by:Ken_Lyon
ID: 31511988
Thanks again for your help. I've split the points between two of your comments that pointed to the steps involved - starting DTC on my machine, and using two connection managers rather than Linked Servers. That should hopefully communicate to people that it needs both.
0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22848845
Very much welcome. Good luck!
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now