Copy Activity Hardcode a column value Excel in data lake to SQL Server

Question

I have an extra column in my SQL Sink destination table called Validation type

When I copy the data from the Excel source I simply want to hardcode the work Validated into this column

I tried adding in 'validated' but got the following error

Message=Invalid column name ''Validated'' is specified while read data from worksheet

I don't want to use a data flow because it should be simple in the copy activity

I thought I could add it to the data set schema but it doesnt look like you can add an extra column there once you have imported schema

Is there any way that I can do this in the copy activity?

Answer 1

Hi @Debbie Edwards ,

Thanks for your query. Just wanted to check if you have got a chance to explore "Add additional columns during copy" feature in copy activity. This feature can be used to add additional data columns to copy along to sink.

Below are few scenarios where you can utilize this feature:

• When copy from file-based source, store the relative file path as an additional column to trace from which file the data comes from.
• Add a column with ADF expression, to attach ADF system variables like pipeline name/pipeline ID, or store other dynamic value from upstream activity's output.
• Add a column with static value to meet your downstream consumption need.
  

Please let us know how it goes.
Thank you