question

JeannieHCollaberaVendor avatar image
0 Votes"
JeannieHCollaberaVendor asked SaurabhSharma-msft answered

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

**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






azure-sql-databaseazure-synapse-analytics
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.

JeannieHCollaberaVendor avatar image
0 Votes"
JeannieHCollaberaVendor answered

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

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.

SaurabhSharma-msft avatar image
1 Vote"
SaurabhSharma-msft answered

@jeanniehcollaberavendor 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.
76829-image.png

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



image.png (16.8 KiB)
image.png (41.6 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.