Azure Data Factory REST API to Service Now - Pagination Issue

Question

We are trying to use Azure Data Factory to pull data from ServiceNow using the REST API Connector.

We have tried working with the specific ServiceNow connector in ADF but this does not perform adequately well for our purposes - we need to use actuals and display values and take frequent small loads, you can't filter display in place so it's too slow.

In simple tests the REST API works fine, the challenge is specifically around pagination. After the initial GET request, the ServiceNow API returns relative links in the response header in the following format;

Link →<url>;rel="first",<url>;rel="next",<url>;rel="last"

The REST Resource dataset has settings for Pagination Rules and the documentation suggests that this can be handled - https://docs.microsoft.com/en-us/azure/data-factory/connector-rest#pagination-support

This generic REST connector supports the following pagination patterns:

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

We can't figure out what to put in the Pagination Rules Key/Value in order have it navigate to the rel="next" URL each time it receives a page of data.

We have tried most of the options described in the documentation - something like this seems close:

            {"paginationRules": {
            "AbsoluteUrl": "Headers.['Link']"}

It seems like the Headers.['Link'] part is formed correctly but isn't specific enough.

Has anyone advise what the setting should be to make this work?

Answer 1

With the REST api you can do pagination by adding this parameter to the link

&sysparm_offset=10000

Limit is default 10000 if you want a lower limit add

&sysparm_limit=300

and adjust offset accordingly.

Answer 2

if my understanding is correct, you want to extract the 3rd <url> from the Link header as the URL of next page. Link →<url>;rel="first",<url>;rel="next",<url>;rel="last"

Pagination rule currently doesn't support expressions such as string extraction in this case.