question

ArulP-3734 avatar image
0 Votes"
ArulP-3734 asked ArulP-3734 commented

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

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?

azure-data-factory
5 |1600 characters needed characters left characters exceeded
Toggle Comment visibility. Current Visibility: Visible to all users

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

SaurabhSharma-msft avatar image
1 Vote"
SaurabhSharma-msft answered ArulP-3734 commented

Hi @arulp-3734,

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.
190706-image.png

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


image.png (26.5 KiB)
· 4
5 |1600 characters needed characters left characters exceeded
Toggle Comment visibility. Current Visibility: Visible to all users

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ArulP-3734 avatar image ArulP-3734 ·

Thanks @SaurabhSharma-msft. 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

0 Votes 0 ·
SaurabhSharma-msft avatar image SaurabhSharma-msft ArulP-3734 ·

@arulp-3734, 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.


0 Votes 0 ·
SaurabhSharma-msft avatar image SaurabhSharma-msft SaurabhSharma-msft ·

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

Thanks
Saurabh

0 Votes 0 ·
Show more comments