question

ShaunBailey-5325 avatar image
0 Votes"
ShaunBailey-5325 asked VellankiNaveen-3603 commented

Data Copy Fails When Upserting NULL Into Primary Key Field

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.


198248-config1.png


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

198275-config2.png


azure-data-factory
config1.png (107.4 KiB)
config2.png (7.2 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

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

Hi @ShaunBailey-5325 ,

Just checking in to see if the below answer helped. If this answers your query, do click 130616-image.png and upvote 130671-image.png for the same. And, if you have any further query do let us know.

0 Votes 0 ·

Hi @ShaikMaheer-MSFT

If we have IDENTITY column on primary key on SQL server then insert works from copy activity but upsert fails with an error. I read somewhere that it was a bug on the SQL upsert and the fix was supposed to be deployed in the 1st week of August. Can you please find out if a fix has been deployed for this issue?

Thanks
Naveen

0 Votes 0 ·

1 Answer

ShaikMaheer-MSFT avatar image
0 Votes"
ShaikMaheer-MSFT answered

Hi @ShaunBailey-5325 ,

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.

198677-image.png

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.




image.png (151.8 KiB)
5 |1600 characters needed characters left characters exceeded

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