I created a SSIS package which contains two "Execute SQL Tasks" components - one inserts/updates the records in Table1 of DB1 and other inserts/updates the records in Table2 of DB2.
I have also enabled the Transaction by setting the TransactionOption = Required at the package level and TransactionOption = Supported at the component level. So either all operations (done by both EST) or none should commit.
In our dev and test env it is working fine. But on production (with cluster MSDTC), few times it throws an error:
Executing the query "EXEC [etl].[uspRptPopulateFactCourseUsage] @Linked..." failed with the following error: "The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled the distributed transaction.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Any suggestions would be useful!!