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 pipeline
.
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 [69]: 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 ?
(My 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