ADF Copy data activity pagination with continuation token with GET request

@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

1. Web Activity: Make the initial GET request.
2. Set Variable Activity: Store the continuationToken.
3. Copy Activity: Use the token in headers and set up pagination rules.
4. ForEach Activity (if needed): To handle multiple requests based on the token.

Hope this helps. Do let us know if you any further queries.

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:

1. Copy Activity: Copy first 1000 items to file
2. Web Activity: Make the initial GET request.
3. Set Variable Activity: Store the continuationToken.
4. 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?

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:

1. Copy Activity: To retrieve first 1000 items and save them to a file
2. Web Activity: Make the GET request.
3. Set Variable Activity: Store the continuationToken.
4. 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!

@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