Azure Data Factory & ServiceNow V2 connector - Schema import failed

Question

Hi, I have started to explore using the ServiceNow V2 Connector in ADF to replace an existing on-prem process using the ServiceNow ODBC driver. I have created Datasets for some of the tables, however when creating the Dataset for the 'Incident' table, I receive a long message with the following key points -

"Schema import failed: The API request to ServiceNow failed. Request Url: https://########.service-now.com/api/now/table/incident?sysparm_limit=300

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 2060669 to bypass the need for pagination."}

The incident table has over 300 columns (lots of custom columns), so I'm assuming the limit is 300 based on this showing in the error message - sysparm_limit=300 ?

I need to only extract around 30 columns from this table, is there a workaround for this? I don't want to use the ServiceNow API just for this table.

Thanks

Darren

Answer 1

Hi @Smith, Darren (IT-LON),

The error message indicates that the import failed due to the Column number limitations. You can try the below workarounds to solve the above issue.

Approach 1:

As you only need 30 columns out of 300 columns, set those column names manually in the ServiceNow API URL. This will import the schema for only the given columns and will overcome the pagination.

Approach 2:

As the error message suggests you can set the sysparm_suppress_pagination_header to true which bypasses the pagination, and you can query it without pagination.

Approach 3:

If its allowed, you can also create a view of required columns in your ServiceNow and access that view in the dataset to avoid the pagination. If the creation view is not allowed, try to make a staging table and copy the data of required columns to it.

Hope this helps.

If the answer is helpful, please click Accept Answer and kindly upvote it. If you have any further questions about this answer, please click Comment.

Answer 2

Appreciate the feedback on this, the first answer from Thoomu to manually define the dataset schema has worked & has enabled me to move forward & test some simple Copy Data tasks from ServiceNow to an Azure SQL DB.

I am interested to know if anyone who has worked with this ServiceNow V2 connector has successfully managed to create a Dynamic Pipeline which copies data from multiple tables with a condition and map the columns to a destination Azure SQL DB?