Copy activity failed because you have multiple concurrent copy activities runs writing to the same file

Magarde, A (Ankit) 0

Hi All,

I am migrating 1000 of SQL tables from on premise SQL server to Azure Blob storage. I am using ADF for each and copy activity to do so. However, while processing the tables in parallel/concurrent, I am getting below error -

Failure happened on 'Sink' side. ErrorCode=AdlsGen2OperationFailedConcurrentWrite,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Error occurred when trying to upload a file. It's possible because you have multiple concurrent copy activities runs writing to the same file '<<filepath>>.parquet'. Check your ADF configuration.,Source=Microsoft.DataTransfer.ClientLibrary,''Type=Microsoft.Azure.Storage.Data.Models.ErrorSchemaException,Message=Operation returned an invalid status code 'PreconditionFailed',Source=Microsoft.DataTransfer.ClientLibrary,'

User's image

Note - I am using max concurrent connection at sink as 1 and still i am getting above error.

AnnuKumari-MSFT 33,476 Reputation points Microsoft Employee

2024-10-25T09:49:46.8233333+00:00

Hi Magarde, A (Ankit) ,

Just checking if you got a chance to look at the previous response. Kindly do let us know if it helped to resolve your issue. Thankyou
Magarde, A (Ankit) 0 Reputation points

2024-10-25T11:44:54.0366667+00:00

No, previous comments was not helpful.

1 answer

AnnuKumari-MSFT 33,476 Reputation points Microsoft Employee

2024-10-24T11:44:29.8633333+00:00
Hi Magarde, A (Ankit) ,

Welcome to Microsoft Q&A platform and thanks for posting your query here.

It seems like you are encountering an error when using Azure Data Factory to copy SQL tables from an on-premises SQL Server to Azure Blob Storage. The error code "AdlsGen2OperationFailedConcurrentWrite" indicates that there is a problem with concurrent writes to the same file in Azure Blob Storage.

The error you are encountering occurs when multiple concurrent activities attempt to write to the same file in Azure Data Lake Storage Gen2 (ADLS Gen2). Even though you've set the max concurrent connections to 1, the issue can still arise due to:

Multiple copy activities targeting the same file path concurrently.

Overlapping pipeline executions (manual or by trigger run)

Ensure Unique File Names for Each Copy Activity . You can include timestamps or GUIDs in the file name to ensure uniqueness . Example: table_name_@{formatDateTime(utcnow(),'yyyyMMddHHmmss')}.parquet

Disable Parallel Execution by setting Sequential option to Truewithin ForEach activity.

Check if there is any overlapping pipeline runs/trigger runs.

Hope it helps. Kindly let us know how it goes. Thankyou.
Please sign in to rate this answer.
Magarde, A (Ankit) 0 Reputation points

2024-10-25T11:49:08.8533333+00:00

Point #2 - I have to migrate 40TB or more data from multiple servers. If i will process sequentially then it will take months to complete my work, hence i am looking for parallel processing.

AnnuKumari-MSFT 33,476 Reputation points Microsoft Employee

2024-10-25T12:43:58.1533333+00:00

Hi Magarde, A (Ankit) ,

Could you please share the full expression used in directory and file name . It looks like the sink file path is not unique for all the iterations which is why data from multiple sources are trying to get into same sink as per the error message. You can also share the full pipeline JSON for better understanding. Thankyou

AnnuKumari-MSFT 33,476 Reputation points Microsoft Employee

2024-10-28T09:09:41.6333333+00:00

Hi Magarde, A (Ankit) ,

Just following up to get the required details to help with the issue resolution , in case if it's not resolved yet. Thankyou

Magarde, A (Ankit) 0 Reputation points

2024-10-28T10:48:52.8466667+00:00

Hi @AnnuKumari-MSFT ,

Please below screenshots of sink setting under copy activity -

Directory -

@concat(variables('Directory'),'/',pipeline().parameters.DatabaseName,'/',variables('SchemaName'))

FileName -

@concat(variables('TableName'),'.','parquet')

Magarde, A (Ankit) 0 Reputation points

2024-10-28T11:01:08.83+00:00

Attaching the JsonPL_2_Extract_And_Load_Staging.txt file with mocked server name and directory paths

AnnuKumari-MSFT 33,476 Reputation points Microsoft Employee

2024-10-29T08:24:12.72+00:00

Hi Magarde, A (Ankit) ,

Thankyou for sharing the pipeline json file and the dataset expression for filepath. It seems file path is fine as it's going to give unique value since it is based on each schemaname/tablename .

You are trying to store the schemaname , tablename in a variable using set variable activity and then utilizing it in the dataset inside copy activity. This may lead to unexpected outcomes as Variables are scoped at the pipeline level. This means that they're not thread safe and can cause unexpected and undesired behavior if they're accessed from within a parallel iteration activity such as a ForEach loop, especially when the value is also being modified within that foreach activity.

Reference: https://zcusa.951200.xyz/en-us/azure/data-factory/control-flow-set-variable-activity#incrementing-a-variable

Instead of using Set variable activity, use the expression @item().SchemaName and @item().TableName directly in the copy activity. Instead of using variables inside foreach and relying on it.

Additionally, there are two parallel workflows in this pipeline. Instead of that, why not separate those in two different pipelines so that monitoring of the pipelines would be much easier. If time is the constraint , you can run both the pipelines parallelly , but keeping it in same pipeline would lead to lots of confusion and complications while the activities from both the workflows would be running.

Hope it helps. Kindly accept the answer by clicking on Accept answer button. Thankyou

AnnuKumari-MSFT 33,476 Reputation points Microsoft Employee

2024-11-04T14:47:01.6266667+00:00

Hi Magarde, A (Ankit) ,

Just checking in to see if the above answer helped. Please do consider clicking Accept Answer as accepted answers help community as well. Also, please click on Yes for the survey 'Was the answer helpful'
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Copy activity failed because you have multiple concurrent copy activities runs writing to the same file

1 answer

Your answer