How to ignore copy activity failure rows in sinking to Cosmos DB in ADF

Question

How to ignore copy activity failure rows in sinking to Cosmos DB in ADF

Peter Ma 206 Microsoft Employee

My cosmos db container has a unique constrain for columns.
There maybe some duplicate rows in source file in blob storage.
How can I set ignore the failure rows in copy activity when sinking to Cosmos DB.

By the way "Fault tolerance" doesn't work in this scenario!

ShaikMaheer-MSFT 29,886 Reputation points Microsoft Employee

2021-07-16T11:35:32.57+00:00

Hi anonymous user-0048 ,

Thank you for posting query in Microsoft Q&A Platform.

Fault Tolerance "Skip Incompatible rows" setting should help on PK violation. Is that the not case with you?

Could you please share details on your copy activity configuration details and Error message details or screenshot. So that we can try to repro scenario and get resolution for you.

Thank you.
Peter Ma 206 Reputation points Microsoft Employee

2021-07-16T17:39:16.163+00:00

Hi @ShaikMaheer-MSFT

Here is the Error message:

Operation on target Copy Data failed: ErrorCode=UserErrorDocumentDBWriteError,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Documents failed to import. Error message:Message: {"Errors":["Encountered exception while executing function. Exception = Error: {\"Errors\":[\"Unique index constraint violation.\"]}\r\nStack trace: Error: {\"Errors\":[\"Unique index constraint violation.\"]}\n at createCallback (script.js:6350:26)\n at Anonymous function (script.js:687:29)"]}
ActivityId: 121121e8-e05a-400d-b309-498215ac7a0d, Request URI: /apps/1226fbb7-a202-4ebf-9f8d-0bf04a09f94d/services/f74ee725-0e2f-402b-ab12-e11b2f74c4a7/partitions/fcc15b0e-6118-40d5-bd48-a6544fe75ca8/replicas/132707862642353341p/, RequestStats:
RequestStartTime: 2021-07-16T16:08:57.7270370Z, RequestEndTime: 2021-07-16T16:08:57.7470650Z, Number of regions attempted:1
ShaikMaheer-MSFT 29,886 Reputation points Microsoft Employee

2021-07-19T11:05:53.697+00:00

Hi anonymous user-0048 ,

Thank you for response and sharing error details. But I would be great if you can share your copy activity details may json of your entire copy activity or screenshots of copy activity configuration. So that We can follow along and reproduce scenario and help on detailed resolution. Thank you.
Peter Ma 206 Reputation points Microsoft Employee

2021-07-19T17:09:27.353+00:00

Hi @ShaikMaheer-MSFT , here is the screenshots of configuration:

ShaikMaheer-MSFT 29,886 Reputation points Microsoft Employee

2021-07-16T11:35:32.57+00:00

Hi anonymous user-0048 ,

Thank you for posting query in Microsoft Q&A Platform.

Fault Tolerance "Skip Incompatible rows" setting should help on PK violation. Is that the not case with you?

Could you please share details on your copy activity configuration details and Error message details or screenshot. So that we can try to repro scenario and get resolution for you.

Thank you.
Peter Ma 206 Reputation points Microsoft Employee

2021-07-16T17:39:16.163+00:00

Hi @ShaikMaheer-MSFT

Here is the Error message:

Operation on target Copy Data failed: ErrorCode=UserErrorDocumentDBWriteError,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Documents failed to import. Error message:Message: {"Errors":["Encountered exception while executing function. Exception = Error: {\"Errors\":[\"Unique index constraint violation.\"]}\r\nStack trace: Error: {\"Errors\":[\"Unique index constraint violation.\"]}\n at createCallback (script.js:6350:26)\n at Anonymous function (script.js:687:29)"]}
ActivityId: 121121e8-e05a-400d-b309-498215ac7a0d, Request URI: /apps/1226fbb7-a202-4ebf-9f8d-0bf04a09f94d/services/f74ee725-0e2f-402b-ab12-e11b2f74c4a7/partitions/fcc15b0e-6118-40d5-bd48-a6544fe75ca8/replicas/132707862642353341p/, RequestStats:
RequestStartTime: 2021-07-16T16:08:57.7270370Z, RequestEndTime: 2021-07-16T16:08:57.7470650Z, Number of regions attempted:1
ShaikMaheer-MSFT 29,886 Reputation points Microsoft Employee

2021-07-19T11:05:53.697+00:00

Hi anonymous user-0048 ,

Thank you for response and sharing error details. But I would be great if you can share your copy activity details may json of your entire copy activity or screenshots of copy activity configuration. So that We can follow along and reproduce scenario and help on detailed resolution. Thank you.
Peter Ma 206 Reputation points Microsoft Employee

2021-07-19T17:09:27.353+00:00

Hi @ShaikMaheer-MSFT , here is the screenshots of configuration:

Answer 1

ShaikMaheer-MSFT 29,886 Microsoft Employee

Hi anonymous user-0048 ,

Thank you for posting in Microsoft Q&A Platform.

There might be two possible causes for this failure. Please check details below.

Root Cause:

Cause 1: If you use Insert as the write behavior, this error means that your source data has rows or objects with same ID.
Cause 2: If you use Upsert as the write behavior and you set another unique key to the container, this error means that your source data has rows or objects with different IDs but the same value for the defined unique key.

Resolution:

For cause 1, set Upsert as the write behavior.
For cause 2, make sure that each document has a different value for the defined unique key.

Please check below link for more details.
https://learn.microsoft.com/en-us/azure/data-factory/connector-troubleshoot-guide#error-message-unique-index-constraint-violation

Hope this will help. Thank you.

----------------------

Please accept an answer if correct. Original posters help the community find answers faster by identifying the correct answer. Here is how.
Want a reminder to come back and check responses? Here is how to subscribe to a notification.

Peter Ma 206 Reputation points Microsoft Employee

2021-07-22T18:01:31.973+00:00

Hi @ShaikMaheer-MSFT ,

Thanks for your reply.

Currently, I'm using "Insert" as the write behavior.

I also have a question which is the rows has duplicates(the data is from our partner which we cannot control), if I update it to "Upsert" does that mean I will still have the same error and also ADF copy activity cannot skip it?
ShaikMaheer-MSFT 29,886 Reputation points Microsoft Employee

2021-07-23T05:47:19.113+00:00
Hi anonymous user-0048 ,

Could you please Accept above Answer if that helps you. Please note, accepting answer will help community too.

Thank you for follow up query,

You can try using Upsert as the write behavior and set another unique key to the container. If still you see same issue then that means your source data has rows or objects with different IDs but the same value for the defined unique key.

Even If upset as write behavior not works then you need to either ask source systems to send proper data or implement dataflows to remove duplicate rows and then load only unique rows.

If you would like to know how to remove duplicate rows you can refer below video on YouTube.
https://www.youtube.com/watch?v=YDRn_arRtjA

Hope this will help. Thank you. Please feel free let us know if any further queries.

---------------------------------

Please accept an answer if correct. Original posters help the community find answers faster by identifying the correct answer. Here is how.

Want a reminder to come back and check responses? Here is how to subscribe to a notification.

How to ignore copy activity failure rows in sinking to Cosmos DB in ADF

0 additional answers

Activity