ADF Copy data activity pagination on a REST service POST request

I'm quite new to working with Azure Data Factory (ADF) and I'm running into a problem.

I want to store data from a RESTAPI into an Azure SQL database. I have to use pagination due to the number of results. Inititally I've setup a copy data activity using a GET request with the pagination rules in ADF configured. That was working like a charm.

However, for my usecase I need the data to be returned in a sorted order, which requires me to make a POST request. I ran tests in Postman which worked as expected but I can't seem to get it to work in my Copy Data activity.

This is an example of the POST body for the first request.

{
 "query":  {
    "field": "name",
    "operator": "=",
    "value": "alex"
  },
  "pagination": {
    "per_page": 5,
  } 
}

This is an example respons:

{
  "pages": {
  "type": "pages",
  "next": {
    "page": 4,
    "starting_after": "1HaSB+xrOyyMXAkS/c1RteCL7BzOzTvYjmjakgTergIH31eoe2v4/sbLsJWP\nIncfQLD3ouPkZlCwJ86F\n"
  },
  "page": 3,
  "per_page": 5,
  "total_pages": 10
  }
}

From this respons I need to take the value from 'starting_after' and include that in request body for the next request. That would look something like this:

{
 "query":  {
    "field": "name",
    "operator": "=",
    "value": "alex"
  },
  "pagination": {
    "per_page": 5,
    "starting_after": "1HaSB+xrOyyMXAkS/c1RteCL7BzOzTvYjmjakgTergIH31eoe2v4/sbLsJWP\nIncfQLD3ouPkZlCwJ86F\n"
  } 
}

My problem is that I can't seem to find out how to get a value from a response body in the ADF pagination configuration into the POST request body.

I've tried working with variables but that doesn't seem to be working the way I liked.

Another approach I could try is to use an 'Until' activity with a nested 'Web' activity to iterate over the requests myself.I can't imagine this is the correct way because that's exactly what the copy data activity is designed for.

I've attached an Image. The pagination section is how it works with a GET request, but it doesn't work with POST. Screenshot

Solution

As per documentation ADF pagination works for below:

This generic REST connector supports the following pagination patterns:

Next request’s absolute or relative URL = property value in current response body

Next request’s absolute or relative URL = header value in current response headers

Next request’s query parameter = property value in current response body

Next request’s query parameter = header value in current response headers

Next request’s header = property value in current response body

Next request’s header = header value in current response headers

It will not support for getting response body value in request body.

I could try is to use an 'Until' activity with a nested 'Web' activity to iterate over the requests myself.

We have the only workaround to is to use until loop and some variables to store the value for next iteration. enter image description here for above image you need to follow below steps:

First take copy activity to copy the data of first iteration where in request body starting_after value in not require.
Then take web activity to get the starting_after value for first iteration.
Then take the Set variable to store the value you got from previous web activity.
Then take until activity withe below activities.

enter image description here

-- First Set variable activity will store the value you got from ousider variable of until activity -- web activity to get the starting_after value for every iteration. -- Copy activity to use that value and copy the data. -- Second set variable to store the starting_after value got from web activity. -- set variable to swap the values of First Set variable with second set variable.

This is sample logic you can develop in ADF for your requirement