Add New Column with Dynamic content in Copy Activity

[Rafaela07]

Hi everyone!

I'm facing a challenge in a copy activity of my pipeline.

Context:

I have a pipeline that makes api calls. The apis use something like the following format /api/emails/{email_id)/forms.
In order to be able to reach the "forms" part, I have first made a Copy activity for /api/emails and stored the results in a lakehouse table. Then I added a lookup activity that extracts the distinct email_ids and feeds them in a for each loop.

Inside ForEach there is another copy activity that utilizes each time the relevant email_id to create the respective relative url (eg. @concat('/api/emails/', item().email_id, '/forms') and proceed with the call. And this works fine.

However the response from @concat('/api/emails/', item().email_id, '/forms') does not have email_id as a property. So basically if I just land the resutls in a table I will not know to which email_id each result is linked to. So my goal is in the final table to have the api's original response columns AND the email_id that will be properly generated in each loop.

In the Mapping configuration of the copy activity I tried adding a new column to utilize the item().email_id but dynamic content doesn't seem to work.

I also tried to make the change via View->Edit JSON code or add the column in the Source configuration "Addiotional Columns", but the first one didn't work and the second one seems to expect a list, not a single value.

FYI the api's response is in json format.

Has anyone come across to something similar? And if yes how did you proceed?
I'll be very gratefull for your input!

Thank you in advance,
Rafaela

[Thomaslleblanc]

Your question mentions Copy Activity, which is only in Fabric enabled resources (Data Factory or Data Engineering). If you just have Power BI/Semantic model, then you would not be able to edit the Copy Activity.

The REST API could be called from PoweryQuery, but you would need access to the API code to add the email to the returned results or store in a parameter

[Thomaslleblanc · Accepted solution]

The cleanest way to “carry” the email_id into the sink is to add it as an extra source column in the Copy activity (inside the ForEach) so it flows through mapping alongside the API payload.

one way to do it:

1. Edit your REST dataset and add a parameter, e.g. EmailId (string).
2. In the Copy activity (inside ForEach), pass the item into the dataset:
   • Dataset parameter EmailId = @Item().email_id
3. In Source → Additional columns:
   • Name: email_id
   • Value: @dataset().EmailId

This avoids using item() directly in the Additional columns and can be easier to maintain.

[Rafaela07]

Thank you very much @Thomaslleblanc !

I will mark it as solution, however I have a further question.
What if the only experience your admin has assigned in the workspace is PowerBI and Data Factory or Data Engineering experiences cannot be reached due to that?
You mentioned that your response is the "cleanest" way to approach this so that makes me think that there are also alternatives and I was wandering if one of them also bypasses the example I've just given.

Thank you again!