Problems connecting ADF to ServiceNow with large tables.

Question

Good day to you all,

We use ADF to retrieve many data from many systems. Also from SNOW (ServiceNow) we had this working and the end of November 2024. Now we encounter problems with the tables who are big. Like sn_customersrvice_case

When I now try to do something like preview data in a source dataset, it gives me an error. Also the same if I do a Import schema. It tells me to change the sysparm_limit in the error message, but I am not able to do any changes. It is not a parameter I can set anywhere.

Part of the error:

The API request to ServiceNow failed. Request Url: https://avex.service-now.com/api/now/table/sn_customerservice_case?sysparm_limit=300&sysparm_fields=...tion%2cuser_input%2cbusiness_service%2cskills%2cfollow_the_sun&sysparm_offset=0, Status Code: BadRequest, Error message: {"error":{"message":"Pagination not supported","detail":"The requested query is too long to build the response pagination header URLs. Please do one of the following: shorten the sysparm_query, or query without pagination by setting the parameter 'sysparm_suppress_pagination_header' to true, or set 'sysparm_limit' with a value larger then 8609 to bypass the need for pagination."}

If I do not get the dataset working, the pipeline won't work too.

Is there any solution to this problem? I know there is an option to use a general REST API. But I rather would like to use the standard ServiceNow connector.

Or do I have to wait till Microsoft will fix this one day? Management is getting nervous since reporting is not producing data at the moment.

Thank you.

Kind Regards, Marc

Answer 1

I think your issue is related to pagination and the sysparm_limit setting because ServiceNow is indicating that the query being built is too large, which prevents ADF from successfully fetching data.

So try to reduce the complexity of the query and you can do that by retrieving the necessary fields (sysparm_fields) so you can reduce the number of fields in your query can help lower the size of the response.

You can set sysparm_suppress_pagination_header to trueThis option is mentioned in the error, and it can be set to suppress pagination headers. This means that ServiceNow will not try to paginate the response, potentially helping with large data retrieval. If ADF supports this parameter through the ServiceNow connector, you should add it to the request. However, as this isn't always exposed directly via ADF connectors, you might need to use a custom REST API connection if the standard connector doesn't support this feature.

As you mentioned, a general REST API connection could be an alternative. You can use the sysparm_limit to control the number of records retrieved in a single request. If you're dealing with large datasets, you may need to implement paging (retrieving data in smaller chunks) and handle the pagination manually. The ADF REST connector can be used for this.

Answer 2

Update: I was just playing with the settings myself. I lowered the pagesize to 7000, and with the second attempt it just ran a time without errors.

Will keep monitoring......

Marc