question

JayaVenkatasubramanian-2440 avatar image
0 Votes"
JayaVenkatasubramanian-2440 asked ·

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

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!

azure-data-factory
· 4
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I would like to update that I did find the retry option in the Web activity. However I would still like to know if it is efficient to call a stored procedure a lot of times, even if I am using a Web activity.
Thanks!

0 Votes 0 ·
HimanshuSinha-MSFT avatar image HimanshuSinha-MSFT JayaVenkatasubramanian-2440 ·

Hello @JayaVenkatasubramanian-2440 ,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet .In case if you have any resolution please do share that same with the community as it can be helpful to others . Otherwise, will respond back with the more details and we will try to help .
Thanks
Himanshu

0 Votes 0 ·

Hello @JayaVenkatasubramanian-2440 ,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet .In case if you have any resolution please do share that same with the community as it can be helpful to others . Otherwise, will respond back with the more details and we will try to help .
Thanks
Himanshu

0 Votes 0 ·
Show more comments

1 Answer

HimanshuSinha-MSFT avatar image
0 Votes"
HimanshuSinha-MSFT answered ·

Hello @JayaVenkatasubramanian-2440 ,
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

·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.