Include dynamic property as source column in ADFv2 copy activity within foreach

Question

I have a Copy activity with an OData endpoint as the source, and an Azure SQL Database table as the sink.

This copy activity is within a Foreach activity, with the items coming from another table. I am using the items iterated through the foreach, within the URL as a query parameter with @item().Name (where Name is the column of the item that I need).

Now, the sink of the copy activity has an extra column Name in which I want to write the current @item().Name in each record being copied. So for all records retrieved for endpoint with Name=X I want to populate the Name with X, etc.

However, when I am in the Copy Activity mapping, I cannot find any way to include the current item along with the other columns coming from the OData feed.

Is there a way to populate one column of the sink from an activity variable and the rest of the columns from the source data?

Answer 1

The easy way will be inserting the data first into a database, then you could use something as explained here: Is it possible to add a column with specific value to data in azure data factory pipeline where you end up using a select statement to add the new column.

If you dont, you may try using one of the functions within data flow. Then you can add @item().name as a variable and join/merge the original data with this new column.

Hope this helped!!