ADF Copy Data Activity with upsert Scenario - inserts data when the key columns are NULL from source instead of update

Arul P 6

Hello Experts, I am trying to load data form a SQLDW table to another table. Need to perform upsert using copy activity. I have 5 columns in SINK defined as key columns. among them 2 columns might come with NULL values. In this case, I see a new record inserted rather than update.

Any alternates here to overcome this NULL issue?

Gary Melhaff - US 6 Reputation points

2022-10-03T23:01:36.633+00:00

The only other workaround I've found is to null handle the missing key columns. I can't simply leave data behind because it doesn't fit ADF's Copy upsert requirements. And I don't want to custom code upserts if I can help it. All these options have pros/cons and all are ugly so you have to pick your poison.

And for those who would look at this and wonder how a unique key can have nullable columns, my question back to them is....what makes you think we have control over our source system designs? In my case I'm reading web logs from google and they have no key...you heard it no key. That's not the only issue with this data...don't get me started. You have to take the columns that you believe can uniquely identify an anonymous persons web behavior session activity and make that your unique key. And some of those attributes don't have values. Its just the way Google Analytics works. There's nothing I can do about that. I think we'll see more and more of this when we try to consume nosql types of data whilst trying to enforce integrity rules that they weren't required the data was collected. Reminds me of the old Vsam/Isam days on mainframe. The more things change the more they stay the same.

2 answers

Saurabh Sharma 23,676 Reputation points Microsoft Employee

2022-04-06T23:08:18.85+00:00

Hi @Arul P ,

Thanks for using Microsoft Q&A!!
If I understand you correctly, you are trying to use Copy activity with Upsert, however, the activity is inserting a new record if NULL comes against few of the key column values.
I have tested this and I could see the same behavior and it looks like NULL is being treated as a new record.

I am checking internally with the products team on this and get back to as soon as I have any updates.
I have not tested this but you can try writing using custom logic and apply additional processing before inserting the source data into the destination table by invoking a stored procedure to do the upsert task instead of using the built-in Upsert. Please refer to the Invoke a stored procedure from a SQL sink for details.

Please let me know if you have any questions.

Thanks
Saurabh
Please sign in to rate this answer.

1 person found this answer helpful.
Arul P 6 Reputation points

2022-04-06T23:26:38.513+00:00

Thanks @Saurabh Sharma . I am moving with stored proc method. Please do let know if there is any update on the upsert part.

Also, I would like to point out, when the upsert option updates the records, is there a way the we can avoid updating a specific column like first insertdate of a record rather just update only the updatedate of the record so that we can maintain the history of a record first inserted and and up[dated later. I dont see any options now.

Arul

Saurabh Sharma 23,676 Reputation points Microsoft Employee

2022-04-07T21:43:02.017+00:00

@Arul P , No, it is not possible to update only through the existing upsert mechanism. If you are going with custom logic for the processing, then you could include this behavior in the stored procedure itself.
Also, I have checked internally and you may need to

Use the custom logic like I mentioned above or

Filter them on the source otherwise they should not be considered as part of the unique/primary key

Otherwise, in Data Flows you can specify Alter Row operator where you can define upsert conditions

Please let me know if you have any other questions.

Thanks
Saurabh

----------

Please let me know if you find above reply useful. If yes, please 'Accept the answer' for the above reply. This will help other community members facing similar query to refer to this solution. Thanks.

Saurabh Sharma 23,676 Reputation points Microsoft Employee

2022-04-11T21:28:19.49+00:00

@Arul P , Please do not forget to "Accept the answer" wherever the information provided helps you to help others in the community.

Thanks
Saurabh

Arul P 6 Reputation points

2022-04-11T22:59:37.453+00:00

Hello Saurabh, I see the issue is still on but what we have is a work around here. Should this be considered as an answer?
Sign in to comment
Dirk Sachse 106 Reputation points

2023-03-21T08:51:31.38+00:00

I just ran into the exact same problem and spent a considerable amount of time to research this. It should be documented somewhere, how the upsert function works and that key columns can't be NULL. Also if you have a series of records with the same key combination within one batch of data, upsert won't work either.

My solution was to replace NULL with something else, but I m not that happy with it.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment