Getting error related to Copy Data Task using ADF

Question

Getting below error when running copy data task while moving parquet data from data lake to azure dedicated datawarehouse, does anyone has any idea on this?

ErrorCode=UserErrorSqlDWCopyCommandError,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=SQL DW Copy Command operation failed with error 'Distribution(s):[55]Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.',Source=Microsoft.DataTransfer.ClientLibrary,''Type=System.Data.SqlClient.SqlException,Message=Distribution(s):[55]Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.,Source=.Net SqlClient Data Provider,SqlErrorNumber=110813,Class=16,ErrorCode=-2146232060,State=1,Errors=[{Class=16,Number=110813,State=1,Message=Distribution(s):[55]Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.,},],'

Answer 1

Hi @Anmol Ganju
Thank you for sharing the details.

The error you're encountering -
Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.

typically points to an issue with how the SQL DW (Azure Synapse Dedicated SQL Pool) handles distributed transactions during Copy activities.

Here are a few suggestions to troubleshoot and resolve it:

• Staging Settings: Ensure that the staging option is enabled in your Copy activity. When moving data from Data Lake to Dedicated SQL Pool, staging (using Azure Blob storage) is recommended to avoid distributed transaction issues.
• Use PolyBase or COPY statement: Confirm that you're using the PolyBase or COPY INTO option in the ADF Copy activity. These methods are optimized for loading into Azure Synapse and reduce transactional overhead.
• Data Volume/Partitioning: Check if your parquet files are too small or highly partitioned. Too many small files can trigger distributed transactions. Try merging files or optimizing partitions before copy.
• Connection Settings: Make sure the connection to Azure Synapse in ADF is configured without additional transaction scopes (sometimes advanced settings or custom configurations might impact it).
• Retry Mechanism: Sometimes transient issues cause this. If possible, enable retries in your pipeline activity settings.

Could you please help us with a few more details so that we can assist you better?

• Is the staging option enabled in your Copy activity?
• Are you using PolyBase or COPY INTO?
• Approximate size and number of files you are trying to copy?
• Any recent changes to your Synapse or ADF configurations?

Once we have this, we can guide you more specifically!

I hope this information helps. Please do let us know if you have any further queries.

---

Kindly consider upvoting the comment if the information provided is helpful. This can assist other community members in resolving similar issues.