Data Copy Fails When Upserting NULL Into Primary Key Field

Question

First, here is the error I am receiving:

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 'Id', table 'ThisIsMyTable.dbo.InterimTable_483044d4-1d64-48af-a5a6-4dbefeee6d9c'; column does not allow nulls. INSERT fails.  
The statement has been terminated.,Source=.Net SqlClient Data Provider,SqlErrorNumber=515,Class=16,ErrorCode=-2146232060,State=2,Errors=[{Class=16,Number=515,State=2,Message=Cannot insert the value NULL into column 'Id', table 'ThisIsMyTable.dbo.InterimTable_483044d4-1d64-48af-a5a6-4dbefeee6d9c'; column does not allow nulls. INSERT fails.,},{Class=0,Number=3621,State=0,Message=The statement has been terminated.,},],'  

When using an Insert, the table loads just fine without any errors. However, when I try to UPSERT, I get the error above. Any ideas? I need to lookup on LoanNumber and upsert based on that column.

I have a Primary Key column named 'Id' setup on my table but no other keys.

Answer 1

Hi @Shaun Bailey ,

Thank you for posting query in Microsoft Q&A Platform.

From error messages, it's saying that system is trying to insert null value in to id column. And as shown below screenshot id column does not allow nulls. Hence ending up with this error.

Kindly cross check your data and see if any nulls are getting inserted and try to avoid them to insert.

Between, what is your source and sink data systems?

Hope this helps. Please let us know how it goes. Thank you.