How to add column to multiple tables

[jpelham]

I am trying to add a date column to multiple tables that I am loading from our sql server. I have been able to add the date column when I only do one table within a copy data activity, but cannot figure out how to apply the column addition to multiple tables. I think the issue is related to the mapping and only being able to import one schema at a time. Is there a way to import schemas for all of the tables in the data load using a dynamic expression? 

[jpelham · Accepted solution]

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!

[jpelham · Accepted solution]

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

[v-kongfanf-msft]

Hi @jpelham ,

Did Houston-ho reply solve your problem? If so, please mark it as the correct solution, and point out if the problem persists.

Best regards,

Adamk Kong

[Houston-ho]

have you consider doing the "add column" steps after the copy activities?

and what is the sink for the copy? if that is SQL, you could run a stored procedures to update multiple tables at once.

[jpelham]

I am taking tables from our on-prem sql server and loading them into a Fabric Lakehouse. How would I go about adding a column after the copy activity?

[Houston-ho]

If you are talking about adding calculated column, use the notebook to create a new column on your lakehouse table. This video is quite good.
https://youtu.be/2RuoHpNZbc4

[jpelham · Accepted solution]

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!

[jpelham · Accepted solution]

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:

[frithjof_v]

If you make a suggestion (Idea) please post a link to the Idea here.

[jpelham · Accepted solution]

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