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.
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?
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.
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!