How to load JSON / REST API to SQL Server in SSIS

REST API is becoming the most popular way to communicate between multiple systems. In this blog post you will learn how to read data from JSON REST API or JSON File and import API to SQL Server Table (or any other target e.g. Oracle, MySQL, Flat File). We will use drag and drop approach (yes no coding !!!) so in just few clicks you can extract data from API and load into SQL Table.

For demo purpose we will read JSON format using SSIS JSON / REST API Source but techniques listed in this article can be applied same way to read other formats such as XML or CSV. To read XML API or File use SSIS XML API / File Source. If your API returns CSV format then use CSV API / File Source. Again all these connectors can read from local file or REST API. Changing source path from URL to local file path will refresh the UI options.

Prerequisites

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

SSIS designer installed. Sometimes it is referred as BIDS or SSDT (download it from Microsoft site).
Basic knowledge of SSIS package development using Microsoft SQL Server Integration Services.
ZappySys SSIS PowerPack installed. Click on the link to download FREE trial.
You have basic familiarity with REST API.
Obtain help file for API you trying to call. API reference usually contains very useful information about following things

REST API Concepts

At high level calling REST API consist following things. You don’t have to understand all to get started but more you learn about each category is very useful.

Find out correct API URL to call your action (e.g. /getSingleOrder, /getAllOrders )
Request Type (e.g. GET / POST / PUT / DELETE )
Authentication / Security requirement (How to pass credentials)
Parameters (pass via URL / Body or Header)
HTTP Headers
Pagination (How to loop through all records when response doesn’t include all records)
API Error Handling (Capturing error or continue on certain error)
API Limits (API call limit / Size restriction / API throttling )

API can be public or it may have some sort of authentication requirement. We have documented many API integration scenarios here if you like to learn more.

Video Tutorial

Configure SSIS JSON Source to Read from REST API or local File

Now let’s look at example in SSIS. We will read data from sample API using SSIS JSON / REST API Source Connector. Our sample API doesn’t have any credentials so you can easily play with it.

Configure Connection – Pass credentials

Most API requires some sort of authentication. If your API requires passing credentials then you can use Raw HTTP Headers to pass credentials or Use correct Connection Manager (e.g. HTTP or OAuth).

There two different connection to call API.

Configure HTTP Connection

You can use HTTP in the following scenario.

Use HTTP connection if your API mention that you have to use your UserID/Password as a credential must be passed as Basic Authentication (BASE64 Encoded) Click here to learn more
Use HTTP Connection if your API is XML SOAP API and it uses WSS Security
(e.g. Workday API Example ) (Select Credentials Type=WSS)
Use HTTP Connection if your API needs Token to be passed via HTTP Header. You already have Token which doesn’t expire. (Select Credentials Type=Token)

Configure OAuth Connection

Another very popular authentication mechanism is OAuth 1.0 or 2.0. Click here to learn more about using OAuth connection in SSIS

Example of JSON/REST API Source

Click here to learn more about using JSON or XML Source to read from API / File.

Below is one example of reading from Smartsheet API (A popular online spreadsheet service like Google docs)

Example: Reading Smartsheet data using SSIS REST API / JSON Source and load into SQL Server Table

Loading data from REST API 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)

Drag and drop a Data Flow into the Control Flow:
Dragging and dropping Data Flow Task into Control Flow
Drag and drop Upsert Destination Component on Data Flow surface
Connect ZS JSON Source to Upsert Destination
SSIS – Data Flow – Drang and Drop Upsert Destination Component into Data Flow
On Upsert Destination select/create a new SQL Connection in Target Connection and then click “New” in Target Table and configure it
Configure SSIS Upsert Destination – Loading REST API Data into SQL Server Table
Click on the OK button of Create Table modal popup and do Column Mappings and click on the OK button to save.
REST API to SQL Server Column Mappings for Upsert Destination
Execute the Package now.
Loading 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

There are many error can occur during runtime. Here are most common errors you may face at runtime.

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 Any REST API to SQL Server using SSIS ( drag and drop approach without coding). We used SSIS JSON / REST API Connector to extract data from REST API. 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: