How to pass a Data Flow Parameter in Key Column in Sink Tanformation while updating a data?

Question

I am implementing SCD Type2 through Data Flow. I having created a Parameter in it where I will pass a column name and this Parameter I am using in Sink Transformation in Key Column.

Passing a parameter in Key Column in Data Flow

I have selected the Add Dynamic Content and then Parameter, after that I selected the parameter I have created in Data Flow. Then it shows like "$Key_col". But when I run the pipeline it gives me an error-

{"message":"at Sink 'sink1'(Line 56/Col 6): Column operands are not allowed in literal expressions. Details:at Sink 'sink1'(Line 56/Col 6): Column operands are not allowed in literal expressions","failureType":"UserError","target":"Update_Existing_Records","errorCode":"DFExecutorUserError"}

Can anyone please tell me how resolve this error or any workaround for this Problem.

Answer 1

Key column doesn't support set with parameter. You only can choose the exist column in sink.

The column name that you pick as the key here will be used by ADF as part of the subsequent update, upsert, delete. Therefore, you must pick a column that exists in the Sink mapping. If you wish to not write the value to this key column, then click "Skip writing key columns".

Please reference: Mapping data flow properties.

The parameter Key_col is not exist in the sink, even if it has the same name.

Update:

Data Flow parameter:

If we want to using update, we must add an Alter row active:

Sink, key column choose exist column 'name':

Pipeline runs successful:

Hope this helps.

Answer 2

Yes, this work. You just need to put single quotes around the parameter value like this:

"'$Key_col'"

I'm using double-quotes for string interpolation in this solution, so paste it in your expression exactly as that.