Upsert failing in Dataflow

Question

I am reading data from multiple json files and loading them into a sql database. There are duplicates in the json files, but in the dataflow i have explicitly selected upsert. yet this fails at the id. What am i missing ?

The ID in the DB is set as the primary key

Answer 1

Hello @@sam nick, Just checking on this!

Did you get a chance to see the suggestion given by KranthiPakala-MSFT in the previous comment?

I was able to reproduce the exact error shown in your scenario!

Reason for Error: When i am trying to insert the Column with PK as shown below, the insert is failing!!!!

Below Source CSV will make it fail!

I have removed the duplicate row from CSV and the activity is now success.

Please make sure to have a look at the Upsert and Insert selection in the Sink settings.

Below is my Table design!

Could you please check the Alter Row condition in your Data Flow?
Please let us know what is the condition you have set on the Alter Row, so that we too ty to repro the issue!!!

Below is the Alter row condition in my Data Flow.

If i use the Pipeline for Orchestrating the Copy from Source to Sink, I see there is no issue in the activity.

Please let us know if you need further help in this matter.

Answer 2

@sam nick Welcome to Microsoft Q&A forum and thanks for reaching out here.

As per the error message, it seems like something wrong with source data which is resulting in this behavior. To ensure the source data is correct, could you please try implementing error row handling in your flow so that if there is anything wrong with few of the source records, you can log them as part of error handling and reprocess them after fixing.

For more info on Error row handling in ADF dataflows, please refer to this document: Handle SQL truncation error rows in Data Factory mapping data flows

Another article related to same: How to Handle SQL DB Row-level Errors in ADF Data Flows

Using this approach, you will be able to identify good rows and bad rows from your source data and you can plan to handle them accordingly.

In case if you don't see any issues with your source data, then please us know.

Thank you