- 1 Introduction
- 2 Prerequisites
- 3 REST API Concepts
- 4 Configure SSIS JSON Source to Read from REST API or local File
- 5 Loading data from REST API to SQL Server
- 6 POST Data to ServiceNow API (Insert, Update or Delete)
- 7 Common Errors
- 8 Deployment to Production
- 9 Conclusion. What’s next?
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.
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.
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 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 (Popular online spreadsheet service like Google docs)
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:
- Drag OLEDB destination on Data Flow surface
- Connect ZS JSON Source to Destination
- On OLEDB destination select / create new SQL Connection and then Click “New Table”
- Click on Mappings tab and click OK to save
- 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
There are many error can occur during runtime. Here are most common errors you may face at runtime.
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)".
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.
REST API Integration with SQL Server | How to extract REST API data in SSIS? | How to read REST API like a table?| How to Query REST API in SSIS | Calling REST API using SSIS. | REAT API to SQL Server | SQL Server to REST API | SSIS REST API Integration | SOAP API Integration | SSIS Web API Integration