Im using azure data factory to copy a table from a mysql source to a sql server destination. Im using the default 'record' functionality. In the copy step, i want to enable upsert. I then need to enter the key columns, and im wondering how to make sure that each table can have its own key column(s).
Tried entering column names, however the end result looks confusing, what is the key then for which table?
CodePudding user response：
In order to give the key columns dynamically, in lookup table, a field called key_column is added for every table_name. Below is the detailed approach.
- Lookup table is taken with fileds table name and key column. In ADF, lookup activity dataset is taken with the lookup table.
- In for-each activity, lookup table output is taken.
- Copy activity is taken in for-each activity. Source dataset is taken like below image.
- In sink dataset, write behaviour is given as 'upsert' and key columns is given dynamically as
@array(item().key_column
- By this way key columns can be assigned dynamically and upsert can be performed in copy activity