Cannot insert null on identity column when using upsert in Copy data

Question

Hi!

In Azure Data Factory I'm using a Copy Data action. I have my source table and my target table. I want to use Upsert in Sink to copy data to the target table.

The target table has a field EmployeeID which is a primary key and has identity to increment with 1.

The column that I compare key data is of course another column. I do all the mapping and remove the mapping between EmployeeID. When I run this I get an error that I cannot insert null in EmployeeID. I tried to do a full insert and it works without the EmployeeID but when I change to Upsert it fails. Why does it want to insert something in EmployeeID, there is no mapping and it can't be.

Any help?

Answer 1

Hi @Magnus ,
Thankyou for using Microsoft Q& A platform and thanks for posting your query.
As I understand your query, you are trying to perform Upsert in SQL table which is having Employee ID as identity column . However, it fails when you are removing EmployeeID column from mapping so that it automatically inserts the EmployeeId value. Please correct me if my understanding is incorrect.

I tried to reproduce your scenario and witnessed same issue. Insert is working fine after removing the identity column from mapping while upsert is failing.

I have raised the issue with internal team , will keep you posted once I hear back from them . Thankyou for your patience!

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

Answer 2

Hi,

If you are having this error, you can remove the ID column from the sink data source schema.
As long as, a "not nullable" column in the sink schema, interim table will not let you send null data as it will not understand if your DB will handle it or not.
Only way to really "not send" it and let DB handle the Identity is to remove it from the schema manually.

In the data source schema click the View

Delete the block for your ID column and click OK.

Answer 3

I had this issue as well. To get around it, we left new records with a null value in the Id column, then split the ADF pipeline into two actions. The first action updates existing records (and includes the Id field in the mapping), the second action inserts new records (and does not include the Id field in the mapping).