Share via

Transactions in Azure Data Factory & SSIS

Khidir Elsanosi 21 Reputation points
2021-02-16T16:04:38.383+00:00

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 Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,872 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. MartinJaffer-MSFT 26,046 Reputation points
    2021-02-16T23:05:16.677+00:00

    Hello @Khidir Elsanosi and welcome to Microsoft Q&A.

    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://learn.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://learn.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://learn.microsoft.com/en-us/azure/azure-sql/database/elastic-transactions-overview#limitations

    0 comments