Why am I getting a 'Cannot insert duplicate key in object' error when I'm copying [SalesLT].[Customer] from Azure SQL Database to Synapse?

Question

**I am following this tutorial to copy tables from AdventureWorks in Azure SQL Database to Synapse using a pipeline within Synapse, not Data Factory

This tutorial uses the Auto Create Table option in the pipeline Sink

Even though there are no duplicates in [SalesLT].[Customer] , I keep getting these errors**

Operation on target Copy data1 failed: 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=Violation of PRIMARY KEY constraint 'PK_Customer_CustomerID'. Cannot insert duplicate key in object 'SalesLT.Customer'. The duplicate key value is (1).
The statement has been terminated.,Source=.Net SqlClient Data Provider,SqlErrorNumber=2627,Class=14,ErrorCode=-2146232060,State=1,Errors=[{Class=14,Number=2627,State=1,Message=Violation of PRIMARY KEY constraint 'PK_Customer_CustomerID'. Cannot insert duplicate key in object 'SalesLT.Customer'. The duplicate key value is (1).,},{Class=0,Number=3621,State=0,Message=The statement has been terminated.,},],'

I deleted the record where SalesLT.Customer.CustomerID = 1 and got this error when I tried running it again.

Operation on target Copy data1 failed: 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=Violation of PRIMARY KEY constraint 'PK_Customer_CustomerID'. Cannot insert duplicate key in object 'SalesLT.Customer'. The duplicate key value is (2).
The statement has been terminated.,Source=.Net SqlClient Data Provider,SqlErrorNumber=2627,Class=14,ErrorCode=-2146232060,State=1,Errors=[{Class=14,Number=2627,State=1,Message=Violation of PRIMARY KEY constraint 'PK_Customer_CustomerID'. Cannot insert duplicate key in object 'SalesLT.Customer'. The duplicate key value is (2).,},{Class=0,Number=3621,State=0,Message=The statement has been terminated.,},],'

There are no duplicate Primary Key values to begin with, why do I keep getting these strange errors?
Thanks in advance

Answer 1

I deleted the database and I'm not able to reproduce the error - I think the problem was in the sink.

I think to avoid errors from now, it's best to preview the schema under Mapping

Thank for your reply

Answer 2

@JeannieH(COLLABERA INC) Thanks for using Microsoft Q&A !!
I have tried the sample and it works fine. Even I run it multiple times. I see Primary key is defined at source table in Adventureorks SQL DB. Can you please share the screenshot of your source and sink connections in case you are doing something wrong at the sink connection.

Target table results after multiple runs (duplicate values are created without any issues).