ADF - Capture ForEach paramters and load into SQL Table

Question

Hi All,

Need advice...

I am using WebActivity component in ADF for GET method from an API. This is how my flow looks.

I am passing two parameters and saving the response from API in the ADLS. later, loading the JSON file into SQL Table along with parameters in the Sink(SQL Table). However, this consumes lot of time as I have close to 1million API calls that I need to make.

I need some advise on how to store the parameters that I am passing through for each loop in my API response so that I can design something like this.

I would like to capture those parameters and load into my sink. Can someone pls suggest some ideas to avoid having the Copy Activity inside ForEach loop.

TIA!

Env: Synapse SQL

Answer 1

Hey,
You can use an array variable and append the For each items using the append variable activity you can save all For each parameters :
http://datanrg.blogspot.com/2018/10/append-variable-activity-in-azure-data.html

and then leverage that variable and convert it into string and pass it to Copy activity