question

arkiboys avatar image
0 Votes"
arkiboys asked MarkKromer-MSFT answered

dataflow - column mapping issue

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

136486-image.png





sink1
136551-image.png

136495-image.png


azure-data-factory
image.png (18.6 KiB)
image.png (36.8 KiB)
image.png (30.3 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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

0 Votes 0 ·

1 Answer

MarkKromer-MSFT avatar image
0 Votes"
MarkKromer-MSFT answered

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.

· 7
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Yes, I understand, however, the sink is dynamic so how do I set the keycolumns in there dynamically?

0 Votes 0 ·

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.

0 Votes 0 ·
arkiboys avatar image arkiboys MarkKromer-2402 ·

Please note that each table may have more than one of keycolumns.
Will your method works for multiple keycolumns?

0 Votes 0 ·
Show more comments