The SSIS Package will run from SQL01.
The SSIS Package contains a single Sequence Container:
The package level Transaction configuration is set to:
The Sequence Container Transaction configuration is set to:The SQL tasks Transaction configuration is set to: (all SQL Tasks)
Both servers contain a test database with the table tblName in it.
CREATE TABLE [dbo].[tblName](
[ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[Name] [nchar](10) NULL
) ON [PRIMARY]
GO
On SQL01 the below inserts are executed:
INSERT INTO [tblName]
([Name])
VALUES
('Arjan')
GO
INSERT INTO [tblName]
([Name])
VALUES
('Pieter')
GO
The table on SQL02 is still empty:
The SQL tasks in the package are executing the following commands:
- SQL Task: SQL Insert Bert Local
INSERT INTO [tblName] ([Name]) VALUES ('Bert')
GO - SQL Task: SQL Insert Bert Remote
INSERT INTO [tblName] ([Name]) VALUES ('Bert')
GO - SQL Task: SQL Insert Key 1 again local
INSERT INTO [tblName]
(ID,[Name]) VALUES (1,'Bert')
GO
I added a Pre-Execute brake point on the last SQL task to make it possible to check the results of the preceding SQL tasks.
Let's first run the package without Distributed Transaction enabled; DTC configuration is the same on both servers:
Let's run the package and look at the result.
We do get an error immediately:
When we take a look at the SSIS Package execution progress see multiple errors.
- On the SQL Insert Bert Remote task the below error is displayed:
[Execute SQL Task] Error: Failed to acquire connection "SQL02.dbAFTest". Connection may not be configured correctly or you may not have the right permissions on this connection. - On the Package level I see the below errors displayed
[Connection manager "SQL02.dbAFTest"] Error: The SSIS Runtime has failed to enlist the OLE DB connection in a distributed transaction with error 0x8004D025 "The partner transaction manager has disabled its support for remote/network transactions.".
[Connection manager "SQL02.dbAFTest"] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x8004D025.
Let's configure DTC on SQL01 and SQL02.
- Logon to the server with a local administrators account
- Click start
- In the search box type dcomcnfg and hit ENTER
- Browse to the Local DTC
- Right click the Local DTC and select properties
- Select the security tab and set configuration as below
I know that Mutual Authentication Required is more secure; this is however not supported on SQL Server Cluster. - Click OK
- A warning will be displayed that a Reset of the DTC service is going to be executed. Click Yes
- A message will be displayed that the reset is completed.
- Do the same on the other server(s)
Let's run the package again and look at the result.
For a longer period then I would like to, the below status of the run is displayed, nothing seems to be happening. Get some coffee! And be patient.
After about 2,5 minutes the following is displayed.Not where I hoped for so let's look at the progress errors.
- On the SQL Insert Bert Remote task the below error is displayed:
[Execute SQL Task] Error: Failed to acquire connection "SQL02.dbAFTest". Connection may not be configured correctly or you may not have the right permissions on this connection. - On the Package level I see the below errors displayed
[Connection manager "SQL02.dbAFTest"] 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".
[Connection manager "SQL02.dbAFTest"] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x8004D00E.
The first time the DTC error displayed: "The partner transaction manager has disabled its support for remote/network transactions."
This time the DTC error displays: "The transaction has already been implicitly or explicitly committed or aborted".
So what did we do wrong or even did forget? When you search the internet for the last DTC error you will find multiple references to the Windows firewall. So let's have a look. On both servers I found that the DTC TCP firewall Inbound/Outbound rules are still disabled.
Let's enable them, I hope you know how, do this on all servers that require to use SSIS and DTC.
With the firewall rules enabled, let's run the package again and look at the result.
This looks promising, before we continue processing let's look at the SQL results on the tables.
SQL Insert Bert Local Task result on server SQL01
SQL Insert Bert Remote Task result on server SQL02
Until now it is as we should aspect. Both tasks completed successful and data is inserted in the tables. The next step should fail!
And lucky us! It does!
Let's look at the progress errors.
- On the SQL Insert Key 1 again local task the below error is displayed:
[Execute SQL Task] Error: Executing the query "INSERT INTO [tblName] (ID,[Name])..." failed with the following error: "Cannot insert explicit value for identity column in table 'tblName' when IDENTITY_INSERT is set to OFF.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
On SQL01 the previous inserted Bert by the SQL Insert Bert Local task is gone!
On SQL02 the table is empty, again.
We are now ready to develop packages with multi server transactions!
See How to Cluster MSDTC for additional information how to configure MSDTC.
8 opmerkingen:
Ziet er goed uit.
Thx for documenting !
This is an excellent document I was exactly looking for. God bless you for such a wonderful documentation. Thanks in deed...
Thank you very much. Your document helped me.
Thanks I have been struggling with this for a while
Op twee w2012R2 machines alles ingesteld incl. firewall maar nog steeds de melding:
[Connection manager "BSP.bsp"] 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".
Wat zou dat kunnen zijn? Is er misschien een restart vereist na instelling?
Marc
Thank you so much for the detailed post, it was very helpful, MSDTC is running on both the servers and I did enable 'Network DTC Access ' 'allow inbound' 'allow outbound' and 'allow remote clients' in both the servers and also made sure that windows firewall was also enabled in both the servers, but after making all these changes, I am still getting below error, can you please help with this.
[Connection manager "AdventureWorks"] 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".
Thank you so much. This worked.
Een reactie posten