How to use the pre-copy script from the copy activity to remove records in the sink based on the change tracking table from the source?

Question

I am trying to use change tracking to copy data incrementally from a SQL Server to an Azure SQL Database. I followed the tutorial on Microsoft Azure documentation but I ran into some problems when implementing this for a large number of tables.

In the source part of the copy activity I can use a query that gives me a change table of all the records that are updated, inserted or deleted since the last change tracking version. This table will look something like

PersonID   Age    Name   SYS_CHANGE_OPERATION
---------------------------------------------
1          12     John   U
2          15     James  U
3          NULL   NULL   D
4          25     Jane   I

with PersonID being the primary key for this table.

The problem is that the copy activity can only append the data to the Azure SQL Database so when a record gets updated it gives an error because of a duplicate primary key. I can deal with this problem by letting the copy activity use a stored procedure that merges the data into the table on the Azure SQL Database, but the problem is that I have a large number of tables.

I would like the pre-copy script to delete the deleted and updated records on the Azure SQL Database, but I can't figure out how to do this. Do I need to create separate stored procedures and corresponding table types for each table that I want to copy or is there a way for the pre-copy script to delete records based on the change tracking table?

Answer 1

Before the Copy Activity, use a LookUp activity and that, you should be able to query the database which will provide you the deleted & updated PersonIDs, mostly in all the field and seperated by comma.

Try this pre-copy script:

delete from TableName where PersonID in (@{activity('MyLookUp').output.firstRow.PersonIDs})

With this, you should be able to delete or update rows even before inserting the new ones.

You can refer to this official document for more help:

https://docs.microsoft.com/en-us/azure/data-factory/control-flow-lookup-activity