I have a package that runs successfully. When I switch it to using transactions (i.e. TransactionOption=Required) it fails. I get the following messages in the log:
-Starting distributed transaction for this container.
-Failed to acquire connection "<connection-manager-name>". Connection may not be configured correctly or you may not have the right permissions on this connection.
-Aborting the current distributed transaction.
So - it seems it is having trouble enlisting that connection manager in a distributed transaction. My connection manager points at a SQL Server database on a different server to where I am running the package.
The connection string on the connection manager is: Data Source=ABENTAH3D02\DEVDBINSTANCE;Initial Catalog=CUESeerMetadata;Provider=SQLOLEDB.1;Integrated Security=SSPI;Auto Translate=False;
OK, so I kind of know where the error is but I don't know exactly what's causing it or how to go about diagnosing the problem. I don't have much experience of MS DTC although I have used it from SSIS in the past successfully.
Does anyone know the pre-requisites for using MS DTC?
How can I go about finding the problem?
Thanks
JamieThis sounds like a DTC issue with your permissions.
I'm not sure what the problem is, but here are a few things to check:
Are you able to enlist in a transaction on the remote machine where the SServer is? Run the package on that machine if you can. Is it successful there?
Is DTC started and running on both machines?
Do you have the priviledges to access the connection on the remote machine?
You might check the Windows Event Viewer on both machines to see if there are any messages that may give you more information about the problem.
HTH,
K|||I don't have the ability to run the package on the SQL box - or more accurately don't have time to install all the pre-reqs- but I'm pretyt sure that it IS a problem enlisting remote connections in the txn. i.e. I have a local connection in my package which enlists in the transaction without a problem.
In my local dtc trace log I have the following messages (many times)
"transaction got begun, description : '<NULL>"
"received request to abort the transaction from beginner"
"transaction is aborting"
"transaction has been aborted"
Nothing in the event viewer.
I'm working on it being a priviledges problem! Unfortunately I have no experience with DTC so its a slow process.
-Jamie|||Jamie, is DTC running on the server machine, i.e. ABENTAH3D02?This can be found out through the following steps:
1. Open "Control Panel"
2. Open "Administrative Tools"
3. Open "Component Services"
4. Expand "Computer", right-click on "My Computer", switch to page "MSDTC"
Look for Service Control Status. Is it "Started"?|||
Runying Mao wrote:
Jamie, is DTC running on the server machine, i.e. ABENTAH3D02?This can be found out through the following steps:
1. Open "Control Panel"
2. Open "Administrative Tools"
3. Open "Component Services"
4. Expand "Computer", right-click on "My Computer", switch to page "MSDTC"Look for Service Control Status. Is it "Started"?
Yeah it is. First thing I checked 
Ok. Good.
Then in the same page "MSDTC", click "Security Configuration". There are "Allow Inbound" and "Allow Outbound" in Transaction Manager Communication. Are they both checked?
|||Ok. Good.
Then in the same page "MSDTC", click "Security Configuration". There are "Allow Inbound" and "Allow Outbound" in Transaction Manager Communication. Are they both checked?
Yes they are!
Can the choice of logon account affect it? Currently it is set to "NT Authority\NetworkService" on both machines.
I've also tried selecting "No authentication required" on the server. It didn't make any difference.
In fact, here are all the settings on the server (as taken from the event log):
MS DTC started with the following settings (OFF = 0 and ON = 1):
Security Configuration:
Network Administration of Transactions = 1,
Network Clients = 1,
Inbound Distributed Transactions using Native MSDTC Protocol = 1,
Outbound Distributed Transactions using Native MSDTC Protocol = 1,
Transaction Internet Protocol (TIP) = 0,
XA Transactions = 0
Filtering Duplicate events = 1
And here are the local settings:
MS DTC started with the following settings:
Security Configuration (OFF = 0 and ON = 1):
Network Administration of Transactions = 1,
Network Clients = 1,
Inbound Distributed Transactions using Native MSDTC Protocol = 1,
Outbound Distributed Transactions using Native MSDTC Protocol = 1,
Transaction Internet Protocol (TIP) = 0,
XA Transactions = 0
-Jamie|||IGNORE THIS!!!!
I've got it working. You've got to turn Windows Firewall off
http://support.microsoft.com/default.aspx/kb/839279
YEEESSSSS!!!!
(Very happy right now
-Jamie|||
This DTC seems not to work...
I have a package with a single data flow that copies a table from Informix to SQL Server. It finishes "all green", but ask me if I got any data on the target table...
