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 ,
We wanted to kindly follow up regarding your query. If you need any further assistance, please reach out.
Thank you.

[BhaveshPatel]

This APIs through Fabric Data Factory is not feasible and not the right way to fetch the data. This is the reason why we have fabric databases/lakehouses..May be 100000 rows is limit....

This approach is not recommended way by Microsoft as well.

[v-veshwara-msft]

Hi @IWILLWIN ,

Just checking in to see if you query is resolved and if any responses were helpful.
Otherwise, feel free to reach out for further assistance.

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!

[v-veshwara-msft]

Hi @IWILLWIN ,

Apologies for the delayed response and thanks for sharing the update and additional observations.

The message around 'HttpOnly' and location.hash suggests that the API is returning a cookie with restricted access. Cookies marked as HttpOnly are not accessible through client-side logic, including Web or Copy activities. This is expected behavior from a security standpoint and limits the ability to extract tokens or session data stored in such headers.

Also, it's worth validating whether the API endpoint you're using is served over https. In some cases, headers like pagination tokens or custom metadata were blocked when the request was made over http, but were accessible when switching to https as mentioned here.

Since the continuation token isn’t accessible directly, and if your API supports page-based access (e.g. via ?page= or similar), another possible pattern is to first determine the total number of pages, then build a list of page numbers using an array variable, and iterate using a ForEach loop. Each iteration passes the page number into a parameterized dataset and writes results to separate output files. This approach avoids dependence on response headers entirely.

You can see a working example of this here:
How to pass Pagination rules during a rest api calls in copy data activity? - Microsoft Q&A

Additionally, limited access to custom response headers has been raised in the Fabric Ideas portal: Custom response headers from a URL using Web Heade... - Microsoft Fabric Community
Currently, only generic headers are exposed, which affects integration with APIs that return key details like authentication tokens or pagination cursors exclusively via headers.

Please upvote this idea if its relevant to your scenario to increase visibility.

Hope this helps. Please reach out for further assistance.

Thank you.

[v-veshwara-msft]

Hi @IWILLWIN ,
Just wanted to check if the response provided was helpful. If further assistance is needed, please reach out.
Thank you.