1

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?

Improve this question
2
  • Hi @Azure Cloud Enthusiast, can you provide a sample of your source and expected output? Commented May 31, 2022 at 14:04
  • [1]: i.sstatic.net/IBORJ.png This is the sample CSV which is having similar kind of data. I have skipped the first row using the skip row option in the Source. Now I need to add the additional column which is having the first row value and insert it to the SQL Table. Commented May 31, 2022 at 14:12

1 Answer 1

Reset to default
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.

enter image description here

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

enter image description here

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

enter image description here

  1. 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

enter image description here

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

enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.