I have a solution, in which an
SSIS package is deployed on
Azure. The package is being executed via
Data Factory (V2) using
Execute SSIS Package task in a
I have a
sequence container within the package, in which I would like to enable the transaction for (all child components inside the container much succeed, otherwise to roll-back).
I changed the transaction option to
Required, however after triggering the
pipeline I got this errors (from SSIS Logs):
Error: 0xC001402C at MyPackage: The SSIS Runtime has failed to enlist the OLE DB connection in a distributed transaction with error 0x80070057 "The parameter is incorrect.".
Error: 0xC0202009 at MyPackage: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80070057. Error: 0xC020801C at MyDataFlow, Missing Codes : SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "My Connection" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
Error: 0xC004701A at MyDataFlow, SSIS.Pipeline: Missing Codes failed the pre-execute phase and returned error code 0xC020801C.
Is there any configuration that I miss on the
SSIS package, or on the
Data Factory (V2) side ?
Azure SQL Database is a standard deployment, with
General Purpose service tier)
I see you have also posted this in Microsoft Q&A which is answered by @MartinJaffer-MSFT. Copying here for easy reference:
We took a look in our records and found where someone else got a similar error. They were trying to migrate integration processes from on-prem to the cloud.
SQL PaaS doesn’t support distributed transactions the same way as on-prem.
For leveraging Azure SQL PaaS (as per your current architecture), you would need to develop a custom C# OR .Net app that handles the workflow: https://docs.microsoft.com/en-us/azure/azure-sql/database/elastic-transactions-overview#installation-and-migration
For leveraging SQL Managed Instance, you would need to user SQL Trust Groups, which are in preview: https://docs.microsoft.com/en-us/azure/azure-sql/database/elastic-transactions-overview#transactions-across-multiple-servers-for-azure-sql-managed-instance Both SQL MI and SQL DB have distributed transactions limitations: https://docs.microsoft.com/en-us/azure/azure-sql/database/elastic-transactions-overview#limitations
User contributions licensed under CC BY-SA 3.0