1

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://learn.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?

JamesAD
  • 13
  • 1
  • 4
  • Hey @JamesAD, did you find a solution to this problem? I ran into the same problem, but I think I am going to use databricks and python to hit SNOW APIs instead. With python I can do pagination a little easier. – viejoEngineer Aug 14 '20 at 15:40

2 Answers2

0

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.

Tommy
  • 161
  • 1
  • 2
  • 15
  • Hi Tommy - thanks, yes we know we can control the offset of the first record returned in this way and this is how the pagination link moves through the pages. However, we're looking for a way to use the Pagination Rules settings such that the pipeline automatically works through each page until all records are returned. We are investigating a workaround in which we create a for each loop to iteratively call the API incrementing the offset each time. Which should work, but it's something what should be possible automatically. – JamesAD Feb 14 '19 at 09:00
  • you can use the End Condition with the empty result – brajesh jaishwal Jun 08 '22 at 07:40
0

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.

David
  • 61
  • 2
  • Hi David - ok, I suspected that this was a limitation. Thanks for letting us know, for now we have a work around that mimics pagination and works as required. Hopefully it will be supported in the future and we can revise the solution so that it is more elegant. – JamesAD Mar 01 '19 at 11:44