Copy Activity's Fault tolerance not working with sql server sink

Question

Our requirement is to copy data from Azure Sql database to Microsoft Sql server (On Azure VM).

Example:
If from source there are 50 rows which contains 5 error rows.

Question 1:
How to skip the 5 error rows and copy the 45 rows to sink ?

We tried this fault tolerance setting( Please refer the attachment)
but copy activity failed with just Foreign key error and the log file just contains only the header as shown below

Timestamp,Level,OperationName,OperationItem,Message

Question 2:
Will the error row handling option be available in dataflow for sql server sink anytime soon?

Answer 1

Hello @Anonymous ,@Prem
Thanks for the ask and using the Microsoft Q&A platform .
Question 1:
The FK voilation is not a supported scernario , please do read more about the same here .
As a workaround , copy all the data from the source to sink in a staging table ( since this is not having FK constarint defined so it will just work fine . ) . Once the records are copied to the use one more copy activity and in the source use the query option and right a query like .

select * from your stagedtable A
JOIN TablewithFKReference B
A.somecol1 = B.somecol2

and it should work .

Please do let me know how it goes .
Thanks
Himanshu
Please do consider clicking on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community members