I have a copy activity pipeline that simply copies cells A6:C9 into sql table.

However, now I need to grab the value in a cell A3 (date) and write it as a new column in sql table.

How can I achieve that? Do I need to use another copy activity? Or it can be done in a single one?

UPDATE: value A3:A3 in Lookup activity.

CodePudding user response：

You may fetch the desired value with a Lookup activity, and then add it to the Copy Data activity as an additional column. Please see the example below.

Start by creating a range parameter on your Excel dataset, so you may provide it dynamically in the pipeline:

Make sure the Range parameter is added in the Connection tab as well.

Next, create a pipeline with a Lookup activity, followed by a Copy Data activity. The lookup activity should provide the range for the cell you want to capture (in my case, B1)

Finally, in the Copy Data activity, insert an additional column and provide it the Lookup activity output.

The expression I used is @activity('Lookup1').output.firstRow.Prop_0. This will add a column called Date with the value in B1, for every row.