How to drop duplicates in source data set (JSON) and load data into azure SQL DB in azure data factory

0
votes

I have a table in SQL DB with primary key fields. Now i am using a copy activity in azure data factory with source dataset(JSON).

We are writing this data into sink dataset(SQL DB) but the pipeline is failing with the below error

"message": "'Type=System.Data.SqlClient.SqlException,Message=Violation of 
 PRIMARY KEY constraint 'PK__field__399771B9251AD6D4'. Cannot 
 insert duplicate key in object 'dbo.crop_original_new'. The 
 duplicate key value is (9161, en).\r\nThe 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__field__399771B9251AD6D4'. Cannot insert 
duplicate key in object 'Table'. The duplicate key value is 
(9161, en).,},{Class=0,Number=3621,State=0,Message=The statement has 
been terminated.,},],'",
2
azureazure-sql-databaseazure-data-factoryazure-data-factory-2
You need to write to a staging environment and then update your SQL DB from there, checking for duplicates or updates along the way. – iamdave
Can you please show us some of your JSON dataset? I tried in "Copy data", there isn't duplicates in settings.i.stack.imgur.com/IJiY3.png – Leon Yue

2 Answers

0
votes

Well, the finest solution would be:

  • Create a staging table in your SQL environment stg_table (this table should have a different key policy)
  • Load data from JSON source to stg_table
  • Write a stored procedure to clean data from duplicates and to load into your destination table

Or if you are familiar with Mapping Data Flows in ADF you can check this article by Mark Kromer

0
votes

You can use Fault tolerance setting provided in copy activity to skip incompatible rows.

Setting image