Add additional column in copy activity using Azure Data Factory

Question

I am trying to add the Additional Column in the copy activity while transferring the data from the CSV file to the SQL Table using Azure Data Factory.

I have given the CSV file location in the source and SQL Table name in the sink by using the copy activity and it is working fine and data is transferred successfully.

Now I am trying to add an additional column to the SQL Table by clicking the "Add Additional Column" option in the Source of copy activity.

Now I am trying to pass the additional column value as a CSV file header which is having only one value.

Is there any way I can pass the CSV file header row which is having only value to the additional column using copy activity in Azure Data Factory?

Answer 1

You can get the first row of your file using the lookup activity and later use this in your addition column of your copy data activity.

Example:

1. Add lookup activity before copy data activity and use the same as your source dataset in lookup. In your dataset do not select the first row as the header so that it is considered 1st row.

2. In Lookup settings, enable the First row only option in the lookup settings.

3. The lookup output will have the value of your first row.

4. Connect lookup to Copy data activity. In Additional columns under source, add a column to store the lookup output value dynamically.

Expression: @activity('Lookup1').output.firstRow.Prop_0

5. Under mapping, include the additional column to map to your SQL column.