Transactions in Azure Data Factory & SSIS

1

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)

azure
ssis
azure-data-factory-2
asked on Stack Overflow Feb 15, 2021 by khidir sanosi

1 Answer

0

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

answered on Stack Overflow Feb 18, 2021 by krishg

User contributions licensed under CC BY-SA 3.0