How to read data from ServiceNow REST API using SSIS

Introduction

ServiceNow one of the most popular Incident management platform used by many. In this blog post you will learn how to get data from ServiceNow REST API using SSIS JSON Source (No coding required). You will also learn other API integration scenarios such as POST, DELETE, PUT API calls.

You will also learn how to paginate response if you have large amount of data (E.g. more than 10000 rows)

Prerequisites

Before we look into Step-By-Step section to calling ServiceNow API in SSIS let’s make sure you met following requirements.

1. SSIS designer installed. Sometimes it is referred as BIDS or SSDT (download it from Microsoft site).
2. Basic knowledge of SSIS package development using Microsoft SQL Server Integration Services.
3. ZappySys SSIS PowerPack installed. Click on the link to download FREE trial.
4. You have basic familiarity with ServiceNow API and you have UserID and Password handy.

Step-By-Step : Accessing ServiceNow data using SSIS

Now lets look at how to fetch data from ServiceNow API using SSIS JSON Source.

Configure SSIS HTTP Connection for ServiceNow REST API

First step to access ServiceNow data is configure HTTP connection on JSON Source. In below example we will use ServiceNow REST API. There are many endpoints for ServiceNow REST API but we will use Table API Service to query table. Now lets look at steps to configure ServiceNow API connection

1. Open SSIS Package and go to Data Flow designer (Click new data flow if missing)
2. Drag and drop  ZS JSON Source from SSIS Data Flow Toolbox
3. Double click JSON Source to configure it as below
   1. First Enter Full URL to access desired API. For example you can enter URL like below (We entered sysparm_limit=2000 to fetch 2000 records in each response. Change this to higher limit to speed up data extract. This number must match on Pagination tab – see next section). Change instance001 to your own instance name.
      
      https://instance001.service-now.com/api/now/v2/table/incident?sysparm_limit=2000

      1	https://instance001.service-now.com/api/now/v2/table/incident?sysparm_limit=2000

   2. Check Use Credentials option
   3. Click New Connection and configure HTTP connection like below. Once done click OK
      

      Configure ServiceNow REST API Connection in SSIS (Basic Auth using UserID, Password)

   4. Click on Select Filter or enter $.result[*] in the filter textbox
      

      Configure SSIS JSON for ServiceNow REST API – Read data from Table

4. Click Preview to see some data. In next section we will configure Pagination to fetch many records.
5. If you like to load data to SQL Server then Simply connect your JSON Source to OLEDB Destination and Create New Table > Click Mappings and Click OK
6. Execute Package

Configure JSON Source for ServiceNow API Pagination

By default ServiceNow API doesn’t return all records from your table. So if you wish to get all records then you must configure Pagination settings like below. For more information on pagination in SSIS read this article.

ServiceNow REST API RFC-5988 Pagination in SSIS

Once you configure API Pagination it will keep fetching data from new URL automatically until no more data found or it hit Max Row limit you specify on UI (In our case we did max 100 rows). Use tool like Fiddler to view HTTP requests made by JSON Source.

Debugging Web API requests using Fiddler

Configure JSON Source for ServiceNow JSONv2 Pagination (older version API)

In this section you will learn how to use older version of API known as ServiceNow JSONv2 Service (another help link here). Syntax for ServiceNow JSONv2 API is below to access table data.

Now lets look at steps to configure ServiceNow API connection

To configure JSONv2 API use above steps for most of the things except following settings

1. Configure URL like below (assuming you want to query sys_db_object table, with 15 record max in each response)
   
   https://instance001.service-now.com/sys_db_object.do?JSONv2&sysparm_record_count=15&sysparm_query=ws_access=true^sys_mod_count=1^ORDERBYsys_id

   1	https://instance001.service-now.com/sys_db_object.do?JSONv2&sysparm_record_count=15&sysparm_query=ws_access=true^sys_mod_count=1^ORDERBYsys_id

2. Configure Pagination Settings like below
   1. Select Mode = Response Attribute Mode
   2. For NextLink Enter $.records[-1:].sys_id
   3. For Suffix for Next URL enter &sysparm_query=sys_id><%nextlink%>^ws_access=true^sys_mod_count=1^ORDERBYsys_id
   4. For Filter enter $.records[*]

Loading ServiceNow data to SQL Server

Once you done configuring JSON Source you can  load data to SQL Server. For example purpose we will load data into SQL Server but you can load into any Target (e.g. Flat file, Oracle, Excel) using Microsoft or ZappySys Destination connectors

To load ServiceNow data into SQL Server perform the following steps (Screenshot is used for demonstration for purpose, your values can be different than below)

1. Drag OLEDB destination on Data Flow surface
2. Connect ZS JSON Source to Destination
3. On OLEDB destination select / create new SQL Connection and then Click “New Table”
4. Click on Mappings tab and click OK to save
5. Execute Package

POST Data to ServiceNow API (Insert, Update or Delete)

There will be a time you like to automate POST actions (e.g. create new incident via API call). Check this article to learn more on how to POST , DELETE, PUT data using API calls

Common Errors

Deployment to Production

Conclusion. What’s next?

In this article we have learned how to load data from ServiceNow API to SQL Server using SSIS ( drag and drop approach without coding). We used SSIS JSON / REST API Connector to extract data from ServiceNow table. JSON Source Connector makes it super simple to parsing complex / large JSON Files or any Web API Response into rows and column so you can load into database like SQL Server. Download SSIS PowerPack to try many other automation scenarios not discussed in this article.

Keywords:

ServiceNow Integration with SQL Server | How to extract ServiceNow data in SSIS? | How to read ServiceNow table? | Calling ServiceNow REST API using SSIS. | ServiceNow to SQL Server | SQL Server to ServiceNow | SSIS ServiceNow API Integration | ServiceNow SOAP API Integration