This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(262.40000000000003, 97%, 35%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
Hello,
In the data factory pipeline I have a dataflow.
source1 --> sink1
I ran the pipeline and then populated the destination dataset in sink1
Then, I added the alter row activity between the source and sink as below but the pipeline when runs gives error.
source1 --> alter row --> sink1
Error
{"message":"Job failed due to reason: Key column(s) should be specified for non-insertable operations. Details:","failureType":"UserError","target":"df_B2S_Upsert","errorCode":"DF-DELTA-KeyColumnMissed"}
Hope the screen-shots below can help
sink1
Please note that the source 1 is dynamic and so this is not only for one incoming table.
The source may be, table1 or table2, etc
That is why I can not set the column mapping
Hope this helps
You need to tell ADF what is your target database key column as part of the upsert operation. That setting is on the sink settings when you select "Allow Upserts" in the sink settings.
Yes, I understand, however, the sink is dynamic so how do I set the keycolumns in there dynamically?
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(137.6, 61%, 23%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EMK%3C/text%3E%3C/svg%3E)
With schema drift, you are utilizing "late binding" for column names. You need to specify the key column for this operation, however. The easiest way to achieve this is either yb passing in a parameter with the column name and setting it in the key columns property, or add a derived column prior to your sink that explicitly maps a column. In the later example, you can call the column "KeyColumn" in the Derived Column and use the byName() metafunction to set it to a key column value.
Please note that each table may have more than one of keycolumns.
Will your method works for multiple keycolumns?
Yes, you can specify an array of key columns: https://learn.microsoft.com/en-us/azure/data-factory/connector-azure-sql-database?tabs=data-factory#sink-transformation
At present I am using one keycolumn for each table for testing...
Some of the tables have more than one keycolumn though.
I get this error when I run the pipeline...
Is it complaining of the tables which have more than one keycolumns whereas I only provide one keycolumn?
Error
{"message":"Job failed due to reason: at Sink 'sink1': java.lang.UnsupportedOperationException: Cannot perform MERGE as multiple source rows matched and attempted to update the same\ntarget row in the Delta table. By SQL semantics of merge, when multiple source rows match\non the same target row, the update operation is ambiguous as it is unclear which source\nshould be used to update the matching target
That's saying that you've not provided uniqueness for the target row updates. Try using a different combination of columns to provide a proper unique composite key.
I See.
Thank you