Upsert fails and not respecting auto increment on primary key

Question

I am trying to leverage Upsert in copy activity to copy data from multiple databases into a target table with the same table schema and with primary key auto increment.

CREATE TABLE [dbo].[data_source_table](
 [PersonID] [int] IDENTITY(1,1) NOT NULL,
 [Name] [varchar](255) NULL,
 [Age] [int] NULL
) ON [PRIMARY]

Since the records were inserted in the original table in the different databases, it is possible that record has the same primary ID generated by the auto increment feature and let say the Name column is unique so I want to use Name as my merge column during the upsert operation. In my Sink settings, Write behavior is set to Upsert and Key columns: Name. In the Mapping section, I only mapped Name and Age column since I don't want to copy PersonID and I want PersonID to auto increment in the target table. However, the copy operation try to put NULL into the primary key column and results in the following error:

"Message": "Failure happened on 'Sink' side. ErrorCode=SqlOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A database operation failed. Please search error to get more details.,Source=Microsoft.DataTransfer.ClientLibrary,''Type=System.Data.SqlClient.SqlException,Message=Cannot insert the value NULL into column 'PersonID', table 'sqldb.dbo.InterimTable_xx'; column does not allow nulls. INSERT fails

How do I tell the copy activity to use auto increment on the primary key when inserting record?

Answer 1

Hi anonymous user-lau ,
Thankyou for using Microsoft Q&A platform and thanks for posting your query.

As I understand your issue, you are trying to perform Upsert in SQL table which is having PersonID as identity column . However, it fails when you are removing PersonID column from mapping so that it automatically inserts the PersonID value. Please correct me if my understanding is incorrect.

Similar issue has been raised by another customer: https://learn.microsoft.com/en-us/answers/questions/856280/index.html

We have reached out to internal team for help on this.

Meanwhile, temporary workaround is to create custom Stored procedure to perform the Upsert using Merge statement.
Please check the following article for the help : Invoke a stored procedure from a SQL sink