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=20 to fetch 20 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.
    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)

      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

      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 Pagination in SSIS

ServiceNow REST API 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

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)
  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

Configure SSIS OLEDB Destination - Loading Xero Data into SQL Server Table

Configure SSIS OLEDB Destination – Loading ServiceNow Data into SQL Server Table

Xero to SQL Server Column Mappings for OLEDB Destination

ServiceNow API to SQL Server Column Mappings for OLEDB Destination

Loading Xero data to SQL Server in SSIS

Loading ServiceNow REST API to SQL Server in SSIS

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

Truncation related error

The most common error you may face when you run an SSIS package is truncation error. During the design time only 300 rows are scanned from a source (a file or a REST API call response) to detect datatypes but at runtime, it is likely you will retrieve far more records. So it is possible that you will get longer strings than initially expected. For detailed instructions on how to fix common metadata related errors read an article "How to handle SSIS errors (truncation, metadata issues)".

Authentication related error

Another frequent error you may get is an authentication error, which happens when you deploy/copy a package to another machine and run it there. Check the paragraph below to see why it happens and how to solve this problem.

Deployment to Production

In SSIS package sensitive data such as tokens and passwords are by default encrypted by SSIS with your Windows account which you use to create a package. So SSIS will fail to decrypt tokens/passwords when you run it from another machine using another Windows account. To circumvent this when you are creating an SSIS package which uses authentication components (e.g. an OAuth Connection Manager or an HTTP Connection Manager with credentials, etc.), consider using parameters/variables to pass tokens/passwords. In this way, you won’t face authentication related errors when a package is deployed to a production server.

Check our article on how to configure packages with sensitive data on your production or development server.

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

Posted in REST API Integration, SSIS JSON Source (File/REST) and tagged , , , .