ADF Copy data activity pagination with continuation token with GET request

Darja Jafanova 0

I am building ETL pipeline in Microsoft Fabric, ADF service to load data from API, but I am having difficulties implementing pagination rules with continuation token. I know that, according to Azure documentation, it should be possible to use this approach - next request’s header = property value in current response body.

The structure I would like to achieve:

make a GET request to https://test.net/abc/tableName?size=1000 using pipeline Web activity, and get a continuationToken from response body as an output

store it in Set Variable activity

use Copy activity to make a GET request to https://test.net/abc/tableName?size=1000&seekOperation=Next with header = continuationToken from response body of Web activity (stored in variable).
Make another GET requests to retrieve all data until continuationToken is non-existent in response body, each time adding token to header.

I am trying pagination rules, so I set something like this, but it is not working:

    "paginationRules": {
        "supportRFC5988": "true",
        "Headers.ContinuationToken": {
            "value": "$.continuationToken",
            "type": "Expression"
        },
        "EndCondition:body.continuationToken": "NonExist"
}

Error I am getting is something like this:

Failure happened on 'Source' side. ErrorCode=RestInvalidPaginationRule,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Invalid PaginationRule, RuleKey='body.continuationToken', RuleValue='NonExist',Source=Microsoft.DataTransfer.ClientLibrary,'

Also, I don't know how to make it do GET request first time in copy activity, using value stored in variable, and then retrieve it from response body of this request iteratively.

In additional headers I added Token: @variables('continuationToken'), but how to make it work iteratively?

I would be really grateful if someone can help me with constructing this logic. As for now, it looks like this:

enter image description here

phemanth 11,640 Reputation points Microsoft Vendor

2024-11-08T19:25:45.7733333+00:00
@Darja Jafanova

Thanks for reaching out to Microsoft Q&A.

It looka like you’re working on a complex ETL pipeline in Azure Data Factory (ADF) and facing challenges with pagination using a continuation token. Let’s break down the steps to implement this correctly.

Steps to Implement Pagination with Continuation Token

Initial GET Request:

Use a Web Activity to make the initial GET request to https://test.net/abc/tableName?size=1000.

Capture the response, specifically the continuationToken, and store it in a variable (let’s call it continuationToken).

Set Variable Activity:

After the Web Activity, add a Set Variable Activity to store the continuationToken from the response. You can use an expression like:JSON
@json(activity('WebActivityName').output).continuationToken

Copy Activity for Pagination:

Add a Copy Activity to handle the data retrieval.

In the Source settings of the Copy Activity, configure it to use the URL: https://test.net/abc/tableName?size=1000&seekOperation=Next

In the Headers section of the Copy Activity, add:JSON
{ "Token": "@variables('continuationToken')" }

Pagination Rules:

In the Copy Activity, set up the pagination rules. Your JSON should look something like this:JSON
"paginationRules": { "supportRFC5988": "true", "Headers.ContinuationToken": { "value": "$.continuationToken", "type": "Expression" }, "EndCondition": { "body.continuationToken": "null" // or "NonExist" based on your API's response } }

Iterative Requests:

To make the Copy Activity iterate until there are no more tokens, ensure that the continuationToken variable is updated after each request. You may need to use a ForEach Activity if your API requires multiple requests based on the token.

clarifications

Invalid Pagination Rule: The error you’re encountering (Invalid PaginationRule) suggests that the EndCondition might not be correctly set. Ensure that the path to the continuationToken in the response body matches exactly what your API returns. If it returns null when there are no more pages, use that in your EndCondition.

First Request Handling: For the first request in the Copy Activity, you can set the continuationToken variable to an empty string or a default value if needed. This way, the first request can be made without any token.

Example Workflow

Web Activity: Make the initial GET request.

Set Variable Activity: Store the continuationToken.

Copy Activity: Use the token in headers and set up pagination rules.

ForEach Activity (if needed): To handle multiple requests based on the token.

Hope this helps. Do let us know if you any further queries.
Darja Jafanova 0 Reputation points

2024-11-11T10:08:54.89+00:00
Hi @phemanth !

Thank you for the clarification. I tried different EndCondition, but only this one worked as expected:

"EndCondition:$.continuationToken": "NonExist"

One more question - what if I need to copy IDs from each response, for the first GET request as well. Then it means I need to have the following structure:

Copy Activity: Copy first 1000 items to file

Web Activity: Make the initial GET request.

Set Variable Activity: Store the continuationToken.

Copy Activity: Use the token in headers and set up pagination rules and copy another items to file

Because if I use copy activity without web activity, it is not possible to retrieve continuation token from the output. What do you think? Is there any other options how it can optimized rather than making two identical GET requests?
Darja Jafanova 0 Reputation points

2024-11-11T10:27:36.9566667+00:00
Hi @phemanth ! Thank you for the clarification.

I tried several approaches, and it seems to me that the one that is actually working is the following:

"EndCondition:$.continuationToken": "NonExist"

Also, now, I am struggling with another problem. What if I need to copy the first batch of items as well? When I make GET request the first time, I am receiving 1000 items, and I want to copy them to file as well.

So, it means I need to construct the following structure:

Copy Activity: To retrieve first 1000 items and save them to a file

Web Activity: Make the GET request.

Set Variable Activity: Store the continuationToken.

Copy Activity: Use the token in headers and set up pagination rules to retrieve another items and save them to a file.

Because first copy activity is not returning continuation token in output, I need to make another GET request with WEB activity to be able to save token in variable and use it in the next GET request. Is there any other option how it can optimized? Thank you in advance!
phemanth 11,640 Reputation points Microsoft Vendor

2024-11-11T18:26:15.19+00:00
@Darja Jafanova You’re on the right track with your approach! To optimize your ETL pipeline and avoid making two identical GET requests, you can streamline the process while still capturing the IDs from the initial response. Here’s a refined structure you can consider:

Optimized Pipeline Structure

Web Activity for Initial GET Request:

Use a Web Activity to make the initial GET request to https://test.net/abc/tableName?size=1000.

Capture the response, which includes the continuationToken and the data you want to copy (e.g., IDs).

Set Variable Activity:

After the Web Activity, add a Set Variable Activity to store the continuationToken from the response.

Use the expression:
@json(activity('WebActivityName').output).continuationToken

Copy Activity for Initial Data:

Use a Copy Activity to copy the IDs from the initial response.

In the Source settings of the Copy Activity, configure the source to point to the output of the Web Activity. You can use the following expression to specify the source:
@activity('WebActivityName').output.body.items

This assumes that the IDs are in an array named items in the response body.

Copy Activity for Paginated Data:

Add another Copy Activity to handle the paginated data retrieval.

In the Source settings, configure it to use the URL:
https://test.net/abc/tableName?size=1000&seekOperation=Next

In the Headers section, add:
{ "Token": "@variables('continuationToken')" }

Set up the pagination rules as you described earlier.

Loop Until No More Data:

If your API supports multiple pages, you can use a ForEach Activity to loop through the requests until there are no more continuation tokens.

Inside the ForEach, you can update the continuationToken variable with the new token from the previous Copy Activity’s output.

Important Considerations

Single Data Copy: By using the output of the initial Web Activity directly in the first Copy Activity, you avoid making two identical GET requests.

Pagination Logic: Ensure that your pagination logic correctly updates the continuationToken after each request so that you continue fetching data until there are no more tokens.

Data Destination: Depending on your destination (e.g., file, database), ensure that the data structure aligns with what you are writing
phemanth 11,640 Reputation points Microsoft Vendor

2024-11-12T18:06:33.9933333+00:00

@Darja Jafanova 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 with more details and we will try to help.

Share via

ADF Copy data activity pagination with continuation token with GET request

Your answer