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

Venkatachalam Selvakumar 1 Reputation point
2021-08-30T17:57:09.04+00:00

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,Message127645-fault-tolerance.png

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

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,892 questions
{count} votes

1 answer

Sort by: Most helpful
  1. HimanshuSinha-msft 19,386 Reputation points Microsoft Employee
    2021-09-08T17:01:43.627+00:00

    Hello @Venkatachalam Selvakumar ,@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