How to add column to multiple tables

It is not a calculated column that I am trying to add. I want to add a column for a date that the data was loaded, and also a column with the pipeline run ID. I can do this for one table easily in the copy data activity, but when loading more than one table, the copy data activity does not support that functionality. There are ways that I can do this through script activities and/or notebooks, but none of those are efficient for our use case. We have hundreds of tables on this specific sql server and won't be loading them all in the same pipeline, so it will be very tedious to do this for specific tables. I plan to make a suggestion for the functionality to be incorporated into the copy data activity. 

Thank you for the help though Houston-ho!

Here is the Idea link: Update Copy Data Activity to allow for adding columns to multiple tables

I found a way to accomplish this. 

First you have to create some parameters for the pipeline:

One is a list of the table names that you want to load and the other is to create the pipeline_RunID parameter for the pipeline_RunID column. The date column will be created in a different step, though you could do it here in a separate parameter if you wanted to use dynamic content like utcNow. 

Next you need to add the ForEach activity and include the followng code in the settings tab of the activity to reference the tableList parameter created in the previous step:

Then add a Copy Data activity in the For Each Activity. Under the source tab you will connect to your sql server and the desired database. Select Query.

In the query field enter this code, or similar depending on your use case:

SELECT *, GETDATE() AS load_date, '@{pipeline().RunId}' AS pipeline_RunID FROM @{item()}

 

On the destination tab connect to your desired lakehouse and enter @item() in the table field: