Azure Data Factory Copy activity REST connector - how to capture response json value to a variable

Question

I am calling a REST Api in a foreach loop thousands of times. I use the REST connector mainly because I would like to set a Retry on the error from the API.
I need to update the Azure Sql database with a value available in the response JSON for every api call made in the foreach loop.

1. Is it efficient to call a stored procedure using the SINK of the copy activity thousands of times? Do I need to worry about alternate approaches?
2. As an alternate approach, how can I build an array of response values and call a stored procedure in one shot? I do not see a way to obtain the response json from the copy activity output. I also don't know how NOT to configure a sink if I don't intend to call stored procedure for each api call.
   3. Does a Web activity have retry option? I am unable to find any such option. I see that it is easier to extract response JSON value using web activity.

Appreciate any inputs!

Answer 1

Hello @Jay V ,
Thanks for the ask and using the Microsoft Q&A platform .

As i understand , you are concerned that if the there are too many calls to the SQL with SP will that be an issue . The short answer is NO .
Every call which you are making is only updating / Inserting a single record and that should be fine . Just in case if you are concerned about the performance hit on the SQL side, please use the wait activity which will add wait between the calls to the SP , ( but delay the pipeline complation time ) , but honestly i will say that is not required .

Please do let me know how it goes .
Thanks
Himanshu
Please do consider to click on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community members