ADF - Copy Data activity from REST to ADLS. API authorization token expires - Reauthenticate during long pagination

Question

ADF - Copy Data activity from REST to ADLS. API authorization token expires - Reauthenticate during long pagination

Dustin 1

Using ADF I am seeking help to see if there is a way to reauthenticate a REST API after the token has expired during copy data step in the pipeline. Pagination is working as expected but once token expires (5 minutes) the call fails. There is no way to extend the token timeout time at the API service so I need to be able to run a POST request again (first step in pipeline) and use dynamic content to add the bearer token response in the header until the entire endpoint has been read.

Thoughts: API endpoint always responds with a status 401 when token has expired, can I trigger an event on that failure and continue in the process? Other ideas are updating token every 4 minutes but I have not determined a good way to do that either.

This person seems to have the same question but no valid response received. https://stackoverflow.com/questions/70862283/adf-manage-token-expiration-in-copy-data-activity

ShaikMaheer-MSFT 38,631 Reputation points Microsoft Employee Moderator

2022-07-14T15:50:13.027+00:00

Hi @Dustin ,

How about handling pagination in loop. Meaning, continue copying pages in loop and every iteration check time or duration and then try to refresh token again and continue copy?

Please let us know how it goes.
Dustin 1 Reputation point

2022-07-18T15:53:26.297+00:00

Thanks @ShaikMaheer-MSFT for your response.

I like the idea and was already trying to go down that path but do not know how to set the offset to a variable that I update after each read. Here is what I have so far but definitely not understanding the dynamic content and using it with offset and end so it only reads 200 at a time.

Proposed solution:

I already have a way to get total records in an end point and assign to a variable.
Until:
Step one is authenticate. Step 2: send a call for 200 records, write them, update a variable called rows copied using an add [add(activity('Store REST responses').output['rowsCopied'], int(variables('Rows Copied'))))], . Use the rows copied to set the start of the offset and keep looping until rows copied >= record count. This means it would copy 200 records at a time, store, perform add to update variable, re-authenticate, pull the next 200 starting at an offest of 200, rinse, repeat.

Here is my structure.
Dustin 1 Reputation point

2022-07-19T14:46:57.707+00:00

I have it worked out now. I will post a full resolution shortly so others can replicate if they need too.
ShaikMaheer-MSFT 38,631 Reputation points Microsoft Employee Moderator

2022-07-22T08:56:06.983+00:00

Hi @Dustin , Just checking if you get chance to share your work around details with community. Thank you.

1 answer

Your answer

ShaikMaheer-MSFT 38,631 Reputation points Microsoft Employee Moderator

2022-07-14T15:50:13.027+00:00

Hi @Dustin ,

How about handling pagination in loop. Meaning, continue copying pages in loop and every iteration check time or duration and then try to refresh token again and continue copy?

Please let us know how it goes.
Dustin 1 Reputation point

2022-07-18T15:53:26.297+00:00

Thanks @ShaikMaheer-MSFT for your response.

I like the idea and was already trying to go down that path but do not know how to set the offset to a variable that I update after each read. Here is what I have so far but definitely not understanding the dynamic content and using it with offset and end so it only reads 200 at a time.

Proposed solution:

I already have a way to get total records in an end point and assign to a variable.
Until:
Step one is authenticate. Step 2: send a call for 200 records, write them, update a variable called rows copied using an add [add(activity('Store REST responses').output['rowsCopied'], int(variables('Rows Copied'))))], . Use the rows copied to set the start of the offset and keep looping until rows copied >= record count. This means it would copy 200 records at a time, store, perform add to update variable, re-authenticate, pull the next 200 starting at an offest of 200, rinse, repeat.

Here is my structure.
Dustin 1 Reputation point

2022-07-19T14:46:57.707+00:00

I have it worked out now. I will post a full resolution shortly so others can replicate if they need too.
ShaikMaheer-MSFT 38,631 Reputation points Microsoft Employee Moderator

2022-07-22T08:56:06.983+00:00

Hi @Dustin , Just checking if you get chance to share your work around details with community. Thank you.

Answer 1

Dustin 1

I am attaching here what I did to resolve this issue. This resolution uses Ellucian Ethos as the example and certain items (headers, etc) may exist differently for your API but you can at least see how to pull down a auth token, attach to a get call, pagination, file writes and clean ups. I added a part about alternate thoughts which I will probably implement here as we begin to build out a large data lakehouse.224484-copy-and-page-through-large-endpoint-in-azure-data.pdf

ShaikMaheer-MSFT 38,631 Reputation points Microsoft Employee Moderator

2022-07-25T17:01:28.957+00:00

This is awesome. Thank you so much for putting so much of efforts for preparing this and sharing with community.

Share via

ADF - Copy Data activity from REST to ADLS. API authorization token expires - Reauthenticate during long pagination

1 answer

Your answer