EndCondition in Query Parameter for API source in Copy Data Activity

[IWILLWIN]

Hi Fabric Community,

I am facing issues while fetching data from an API source using pagination's query parameters in copy data activity.

Let me explain the scenario in detail :-

I have an API data source from which I want to fetch the data and store it inside the lakehouse (Files).

The API endpoint support pagination. In a single fetch I am getting 100000 rows.

To fetch the next data I am getting next_token in the response header.

Example:-

Following is the API endpoint URL :-

https://.. site ..com/Odata.svc/... source object ...?$format=json_compact&$filter=Date ge '2025-03-17'&$pagination=true

In this URL we have 3 query parameters format, filter and pagination.

To fetch the data using pagination we need to set pagination as true, as show in the above URL.

Once I hit on this URL I get 100000 rows in the response. In the header section of the response I get the next token.

The header name for the next token is "X-MTAPI-NextToken".

The value inside X-MTAPI-NextToken looks like this - $format=json_compact&$filter=Date ge '2025-03-17' and Id gt '1751015724001872115556299804865684'&$pagination=true

The only change we can see over here is the filter query parameter.

To fetch the next chuck of the data the URL will change and it will look like this :-

https://.. site ..com/Odata.svc/... source object ...?$format=json_compact&$filter=Date ge '2025-03-17' and Id gt '1751015724001872115556299804865684'&$pagination=true

After hitting on this URL, I get next chuck of data.

If all the data has been fetched then in the X-MTAPI-NextToken I will get "None" (as per API documentation). In Postman I cannot see X-MTAPI-NextToken.

In copy data activity, for query parameter I have tried all the possible combination.

The data is getting fetched from 1st hit but it is not able to fetch the data from next hit.

Following is screenshot of one of the way used in pagination rule :-

I have also tried with const option, keeping the value as None. But nothing seem to work.

It gets failed when I keep const and None. When I keep NonExist option in EndCondition, the pipline never stops.

The last time I ran, it ran for 12 hr.

Any help or ideas to debug this issue would be really appreciated.

Thank you!

[v-veshwara-msft]

Hi @IWILLWIN ,

Thanks for posting in Microsoft Fabric Community.

Based on your description and the pagination setup shown in the image, it appears that the API response body is returned as a raw array and the continuation token is passed only through the response header X-MTAPI-NextToken. The value of this token is a full query string, which includes the updated $filter with the Id gt condition.

As per the documentation, pagination using JSONPath requires the response body to be a single JSON object. When the body is an array, Fabric cannot apply JSONPath-based EndCondition rules. If the pagination token appears only in the header and the body is not structured as an object, the built-in pagination mechanism in Copy Data activity is not reliable.

https://learn.microsoft.com/en-us/azure/data-factory/connector-rest?tabs=data-factory#pagination-sup... 

To handle this, one approach is to switch to a manual loop using Web and Until activities. You can make the API call using a Web activity, extract the token from the response headers, store it in a variable, and continue looping until the token value is "None". Inside the loop, use Copy Data to fetch data using the token as part of the $filter query, and update the token in each iteration.

Some useful resources: Solved: Fabric Data Factory Copy Activity Pagination Rules... - Microsoft Fabric Community

Implementing Pagination with the Copy Activity in Microsoft Fabric | Microsoft Community Hub

Hope this helps. Please reach out for further assistance.

Thank you.

[IWILLWIN]

Hi @v-veshwara-msft 

Thank you for your response.

As per my understanding the response body is an JSON object.

In Postman, the raw body response looks like this :-

{

  "__count": 100000,

  "results": [

       {"ID":"12345", "Name": "XYZ"},

       {"ID":"67890", "Name": "ABC"},

       ............

   ]

}

I went through the attached microsoft learn link and tried few things.

I used JSONPath i.e $.header.token to access the response header token in end condition.

Attached is the screenshot of the pagination rule :-

Using this pagination rule the pipeline ran in 47 sec with successded status.

I checked the json file in adls, it contained 100000 rows only.

Before trying the second approach, I took a simple web activity and configured it with same URL (1st Fetch URL).

In the output of the web activity, under headers section I was not able to find x-mtapi-nexttoken.

Weird! Maybe that explains why I got only 100000 records in json file.

In Postman using the same URL (1st Fetch URL), I get x-mtapi-nexttoken in response header.

Attached is the screenshot of the response section of web activity output :-

We cannot set 'httpOnly' attribute in this cookie due to below two reasons:

1. 'httpOnly' cookie can only be set by the server and not by any client side code.

2. We do not have access to location.hash in app-gatekeeper

How can I solve this or approach this problem?

Thank you!