How to achieve pagination in Copy activity for POST API

Question

How to achieve pagination in Copy activity for POST API

Tarun Kaushik 0

We have a POST API and as per the current process passing page number starting '0' dynamically in the request body to get the data on a particular page.

We are getting header and rows in the response body.

If rows exists in response body that means data is there and it should be copied to BLOB. If rows not there it will come out of the pagination loop.

--> Need to achieve this using copy activity pagination.

Ganesh Gurram 7,295 Reputation points Microsoft External Staff Moderator

2025-01-28T12:23:43.4+00:00

Hi @Tarun Kaushik

Greetings & Welcome to the Microsoft Q&A forum! Thank you for sharing your query.

To achieve pagination in a Copy activity for a POST API, you can utilize the pagination rules provided by Azure Data Factory. Since you are passing the page number dynamically in the request body, you can configure the pagination rules to handle this.

Set Up Pagination Rules - You need to define pagination rules in your dataset. Since you're using a POST API and passing the page number in the request body, you can set the pagination rule to reference the page number variable in the request body.

End Condition - You should specify an end condition to exit the pagination loop when there are no more rows in the response body. This can be done by checking if the rows exist in the response. If the rows do not exist, the pagination will stop.

In your Copy activity, you will need to set the pagination rules to increment the page number dynamically. For instance, you can use a variable in the request body like {"page": {pageNumber}} and set the pagination rule to increment this variable.

Use the EndCondition to check if the response body has rows. If the rows array is empty, you can set the condition to exit the loop.

By configuring these settings, you can effectively manage pagination for your POST API in Azure Data Factory.

Reference: Copy and transform data from and to a REST endpoint by using Azure Data Factory

https://datakuity.com/2022/07/01/perform-pagination-in-azure-data-factory/

I hope this information helps.

Kindly consider upvoting the comment if the information provided is helpful. This can assist other community members in resolving similar issues.
Tarun Kaushik 0 Reputation points

2025-01-28T18:18:43.91+00:00
Hi Ganesh,

Thank you for your response.

I understood the approach mentioned by you and went through the document as well.

However, I am new to ADF and getting multiple errors to implement. It would be helpful if you can guide with the syntax as well. Below is the details current situation:

I am reading a template from the BLOB location through lookup activity which is being passed to copy activity and in the request body of the POST API at source.

This template has startTime,endtime and Page number variables which are being replaced with the current time and Page Number.

Below is the sample request body: @replace(replace(replace(replace(string(activity('AC_LKPUP_GET_TEMPLATE').output.firstRow),'<startTime>',variables('var_startTime')),'<endTime>',variables('var_endTime')),'<page>',variables('var_pageNo')))

The output of the API would look like:

Head with always be there whereas row would be available only if the current page is having data else it would not be there.

Currently, we are hitting the API using until loop and increasing the value of pageno. and its working fine.

But, I need to achieve this using pagination. So, what I would have to pass in the key and value in the pagination, can you please help. in the request body page number should increase and in the end condition it should run till it is having row keyword in the response body.
Ganesh Gurram 7,295 Reputation points Microsoft External Staff Moderator

2025-01-29T16:55:44.35+00:00

@Tarun Kaushik - Please refer to the videos below to assist in implementing pagination for the POST API in the Copy activity.

https://www.youtube.com/watch?v=pd0JN_8rC3w

https://www.youtube.com/watch?v=lmHRBPOy4Bs

Thank you.
Tarun Kaushik 0 Reputation points

2025-01-30T08:17:38.5666667+00:00
Hi Ganesh,

I have already gone through with these videos and as per my understand my scenario is different from the ones mentioned in videos.

For me,

I have a fixed API URL and it is not changing with page number or next page so I can not do using query parameter

In request body, we have to pass the page number dynamically and in the response we do not have any next page url or total page count( so we can not use absolute url I believe)

In the response body, we have rows which we can put in the end condition to stop the loop

I have passed a variable in the request body, now the main query is what option should I use out of absolute url, query parameter or header etc. in the NAME. For VALUE, also what to choose from the drop down, currently I am using 'body' and passing a logic to increment the page number. I have tried multiple options in the NAME but its giving invalid pagination rule.

Can you please help, what I need to give in NAME and VALUE in the pagination rule for increasing the page number which should increase the variable in the request body automatically.

If I give only end condtion and do not give incremental, and also default page number is set to '0' then it loading the page 0 data correctly

So, the issue here is with the incremental condition. Can you please in framing this condition.

let me know if you need more input for this.

Thank You
Ganesh Gurram 7,295 Reputation points Microsoft External Staff Moderator

2025-01-30T18:05:11.22+00:00

@Tarun Kaushik - Thank you for the clarification! I see that the main challenge here is getting the pagination rule to work with the variable in the request body and increment the page number correctly.

Based on your explanation, the main question is how to set up the pagination rule for incrementing the page number when you're passing it in the request body. Let's break down the steps clearly to avoid any confusion:

Pagination Rule Configuration - When you're dealing with the request body and need to pass the page number dynamically, "Body" should be used in the Name field, as you mentioned you’re passing the page number in the body.

Correct Setup for Name and Value:

Name - In the pagination rule, use body in the Name field because you're passing the page number in the request body.

Value - In the Value field, you want to refer to the variable you're using for the page number and increment it. The correct expression to dynamically increment the page number in the body is:

```@{add(variables('pageNumber'), 1)}`

This will increment the pageNumber variable by 1 with each iteration of the pagination.

Hope this helps.
Ganesh Gurram 7,295 Reputation points Microsoft External Staff Moderator

2025-01-31T03:57:19.6+00:00

@Tarun Kaushik - 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.
Tarun Kaushik 0 Reputation points

2025-01-31T05:31:07.4333333+00:00
Ganesh,

We are on the same page now regarding the problem.

Now, coming to your suggestion in the Name, in the drop down list we have below options:

None

Absoluteurl

Query parameter

Header

MaxRequest Number

End Condition There is no option of body in the "Name". So, as I shared the screenshot earlier I choose none from the dropdown and given pageno. Coming to Value: the available options are

None

Body

Headers

Range Here, I choose none and then passed the incremental logic. because when I choose Body here it is concatenating "$." with the incremental logic which is again throwing invalid rule error. Looking forward to hear more from your side. Let me know if more inputs are required from my side. Thank you
Ganesh Gurram 7,295 Reputation points Microsoft External Staff Moderator

2025-02-03T05:24:29.0833333+00:00

@Tarun Kaushik - Apologies for the inconvenience.

Please reach out to our support team to gain deeper insights and explore potential solutions. It's highly recommended that you reach out to our support team. Their expertise will be invaluable in suggesting the most appropriate approach.

After creating a Support ticket, please provide the ticket number as it would help us to track for more information. Azure support
Khunt, Kajal 0 Reputation points

2025-02-28T16:01:38.31+00:00

@Tarun Kaushik Did you find any solution? I am also struggling with pagination rules to pass page number in body incrementally. Please let me know, if you find any solution. I am able to achieve it with until loop but I need to do it with single copy activity and pagination rules.
Tarun Kaushik 0 Reputation points

2025-02-28T17:36:41.3366667+00:00

Hi Kajal,

Unfortunately, we have come to conclusion that it is not supported by current ADF pagination. So, we are implementing using until only.

https://theonetechnologies.com/blog/post/streamlining-data-extraction-and-pagination-in-azure-data-factory-etl-pipelines#pagination-in-azure-data-factory

You can refer to above page and they are also suggesting through until only.

Anyway, do let me know if you find any other solution
Khunt, Kajal 0 Reputation points

2025-02-28T18:59:24.2666667+00:00

Thank you Tarun. I am also using until to do pagination. Will update you anything new.

Your answer

Ganesh Gurram 7,295 Reputation points Microsoft External Staff Moderator

2025-01-28T12:23:43.4+00:00

Hi @Tarun Kaushik

Greetings & Welcome to the Microsoft Q&A forum! Thank you for sharing your query.

To achieve pagination in a Copy activity for a POST API, you can utilize the pagination rules provided by Azure Data Factory. Since you are passing the page number dynamically in the request body, you can configure the pagination rules to handle this.

Set Up Pagination Rules - You need to define pagination rules in your dataset. Since you're using a POST API and passing the page number in the request body, you can set the pagination rule to reference the page number variable in the request body.

End Condition - You should specify an end condition to exit the pagination loop when there are no more rows in the response body. This can be done by checking if the rows exist in the response. If the rows do not exist, the pagination will stop.

In your Copy activity, you will need to set the pagination rules to increment the page number dynamically. For instance, you can use a variable in the request body like {"page": {pageNumber}} and set the pagination rule to increment this variable.

Use the EndCondition to check if the response body has rows. If the rows array is empty, you can set the condition to exit the loop.

By configuring these settings, you can effectively manage pagination for your POST API in Azure Data Factory.

Reference: Copy and transform data from and to a REST endpoint by using Azure Data Factory

https://datakuity.com/2022/07/01/perform-pagination-in-azure-data-factory/

I hope this information helps.

Kindly consider upvoting the comment if the information provided is helpful. This can assist other community members in resolving similar issues.
Tarun Kaushik 0 Reputation points

2025-01-28T18:18:43.91+00:00

Hi Ganesh,

Thank you for your response.

I understood the approach mentioned by you and went through the document as well.

However, I am new to ADF and getting multiple errors to implement. It would be helpful if you can guide with the syntax as well. Below is the details current situation:

I am reading a template from the BLOB location through lookup activity which is being passed to copy activity and in the request body of the POST API at source.

This template has startTime,endtime and Page number variables which are being replaced with the current time and Page Number.

Below is the sample request body: @replace(replace(replace(replace(string(activity('AC_LKPUP_GET_TEMPLATE').output.firstRow),'<startTime>',variables('var_startTime')),'<endTime>',variables('var_endTime')),'<page>',variables('var_pageNo')))

The output of the API would look like:

Head with always be there whereas row would be available only if the current page is having data else it would not be there.

Currently, we are hitting the API using until loop and increasing the value of pageno. and its working fine.

But, I need to achieve this using pagination. So, what I would have to pass in the key and value in the pagination, can you please help. in the request body page number should increase and in the end condition it should run till it is having row keyword in the response body.
Ganesh Gurram 7,295 Reputation points Microsoft External Staff Moderator

2025-01-29T16:55:44.35+00:00

@Tarun Kaushik - Please refer to the videos below to assist in implementing pagination for the POST API in the Copy activity.

https://www.youtube.com/watch?v=pd0JN_8rC3w

https://www.youtube.com/watch?v=lmHRBPOy4Bs

Thank you.
Tarun Kaushik 0 Reputation points

2025-01-30T08:17:38.5666667+00:00

Hi Ganesh,

I have already gone through with these videos and as per my understand my scenario is different from the ones mentioned in videos.

For me,

I have a fixed API URL and it is not changing with page number or next page so I can not do using query parameter

In request body, we have to pass the page number dynamically and in the response we do not have any next page url or total page count( so we can not use absolute url I believe)

In the response body, we have rows which we can put in the end condition to stop the loop

I have passed a variable in the request body, now the main query is what option should I use out of absolute url, query parameter or header etc. in the NAME. For VALUE, also what to choose from the drop down, currently I am using 'body' and passing a logic to increment the page number. I have tried multiple options in the NAME but its giving invalid pagination rule.

Can you please help, what I need to give in NAME and VALUE in the pagination rule for increasing the page number which should increase the variable in the request body automatically.

If I give only end condtion and do not give incremental, and also default page number is set to '0' then it loading the page 0 data correctly

So, the issue here is with the incremental condition. Can you please in framing this condition.

let me know if you need more input for this.

Thank You
Ganesh Gurram 7,295 Reputation points Microsoft External Staff Moderator

2025-01-30T18:05:11.22+00:00

@Tarun Kaushik - Thank you for the clarification! I see that the main challenge here is getting the pagination rule to work with the variable in the request body and increment the page number correctly.

Based on your explanation, the main question is how to set up the pagination rule for incrementing the page number when you're passing it in the request body. Let's break down the steps clearly to avoid any confusion:

Pagination Rule Configuration - When you're dealing with the request body and need to pass the page number dynamically, "Body" should be used in the Name field, as you mentioned you’re passing the page number in the body.

Correct Setup for Name and Value:

Name - In the pagination rule, use body in the Name field because you're passing the page number in the request body.

Value - In the Value field, you want to refer to the variable you're using for the page number and increment it. The correct expression to dynamically increment the page number in the body is:

```@{add(variables('pageNumber'), 1)}`

This will increment the pageNumber variable by 1 with each iteration of the pagination.

Hope this helps.
Ganesh Gurram 7,295 Reputation points Microsoft External Staff Moderator

2025-01-31T03:57:19.6+00:00

@Tarun Kaushik - 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.
Tarun Kaushik 0 Reputation points

2025-01-31T05:31:07.4333333+00:00

Ganesh,

We are on the same page now regarding the problem.

Now, coming to your suggestion in the Name, in the drop down list we have below options:

None

Absoluteurl

Query parameter

Header

MaxRequest Number

End Condition There is no option of body in the "Name". So, as I shared the screenshot earlier I choose none from the dropdown and given pageno. Coming to Value: the available options are

None

Body

Headers

Range Here, I choose none and then passed the incremental logic. because when I choose Body here it is concatenating "$." with the incremental logic which is again throwing invalid rule error. Looking forward to hear more from your side. Let me know if more inputs are required from my side. Thank you
Ganesh Gurram 7,295 Reputation points Microsoft External Staff Moderator

2025-02-03T05:24:29.0833333+00:00

@Tarun Kaushik - Apologies for the inconvenience.

Please reach out to our support team to gain deeper insights and explore potential solutions. It's highly recommended that you reach out to our support team. Their expertise will be invaluable in suggesting the most appropriate approach.

After creating a Support ticket, please provide the ticket number as it would help us to track for more information. Azure support
Khunt, Kajal 0 Reputation points

2025-02-28T16:01:38.31+00:00

@Tarun Kaushik Did you find any solution? I am also struggling with pagination rules to pass page number in body incrementally. Please let me know, if you find any solution. I am able to achieve it with until loop but I need to do it with single copy activity and pagination rules.
Tarun Kaushik 0 Reputation points

2025-02-28T17:36:41.3366667+00:00

Hi Kajal,

Unfortunately, we have come to conclusion that it is not supported by current ADF pagination. So, we are implementing using until only.

https://theonetechnologies.com/blog/post/streamlining-data-extraction-and-pagination-in-azure-data-factory-etl-pipelines#pagination-in-azure-data-factory

You can refer to above page and they are also suggesting through until only.

Anyway, do let me know if you find any other solution
Khunt, Kajal 0 Reputation points

2025-02-28T18:59:24.2666667+00:00

Thank you Tarun. I am also using until to do pagination. Will update you anything new.

Share via

How to achieve pagination in Copy activity for POST API

Your answer