How to Call REST API in SSIS - Read JSON / XML / CSV

REST API is becoming more and more popular in Data Integration landscape. If you are SSIS Developer (not a coder) and stumbled upon a question what is REST API and “how to consume API inside SSIS package?” then you are at the right place :). We encourage you to read this article carefully and follow various links we provided.

In this article you will learn about many REST API Tips / Tools on the topic how to call REST API using SSIS REST API Task , JSON Source Connector or XML Source Connector. You will also learn how to perform HTTP GET Request and HTTP POST Request without knowing any programming languages (e.g. C#, JAVA, Python)… simply do Drag and Drop in SSIS.

What is REST API / RESTful Web Service

So lets first understand What is REST API or sometimes referred as RESTful Web Service. This is new buzzword which you will hear a lot. More and more services are available in Cloud which makes it obvious to come up with some way so you can access data more firewall friendly manner. What can be better option than access it using several decade old HTTP protocol so no additional configuration required for anybody accessing Cloud Services?

So in short highlights for REST Web Service

REST Web Service is stateless client-server service model
By passing HTTP verb you can perform server side action over standard HTTP protocol (e.g. GET, POST, LIST, DELETE,HEAD)
You can pass parameters via URL query string and via HTTP Headers
If you doing HTTP POST then you can pass additional data in Request Body along with other two method described above
REST API works by sending HTTP Request and Getting HTTP Response
HTTP Response of Web Service can contain Headers and Response Data which can be Binary format, Text format, JSON, XML etc.
You can secure your data by simply sending it over HTTPS (Secure HTTP) protocol or encrypting values passed along with your request

Tasks/Components in SSIS for Consuming RESTful API / WebService

	JSON Source Connector (Read from REST API, JSON File or OData Service): Use this dataflow component when you have to fetch data from REST API webservice like a table. This component allows you to extract JSON data from webservice and de-normalize nested structure so you can save to Relational database such as SQL Server or any other target (Oracle, FlatFile, Excel, MySQL). This component also supports reading local JSON files or direct JSON string (Wildcard pattern supported too e.g. c:\data\file*.json).
	Web API Destination Connector (POST data to API URL) : Use this dataflow component when you have to load data into target system via API calls (POST to URL). E.g. Loading Contacts into Marketing System like MailChimp for email campaign. Loading documents into CRM or document storage systems e.g. Couchbase or ElasticSearch.
	REST API Task : Use this task when you don’t want to pull REST API data in tabular format but want to call rest API for POST data to server, DELETE data from server or things like download HTML page, extract Authentication tokens etc where you not necessarily dealing data in tabular format. This task also allows you many other options such as saving RAW response into variable or file.
	XML Source Connector (SOAP, File, REST) : Use this dataflow component when you have to fetch data from XML or SOAP webservice and consume data like a table. This component allows you to extract data from webservice and save to SQL Server or any other target (Oracle, FlatFile, Excel, MySQL). This component also supports reading local XML files or direct XML string.
	CSV Source Connector (File, REST) : Use this dataflow component when you have to fetch data from File or REST API which returns data in CSV format . This component allows you to extract data from webservice and save to SQL Server or any other target (Oracle, FlatFile, Excel, MySQL). This component also supports reading local CSV files or direct CSV string.

Using SSIS JSON Source to read from REST API and load into SQL Server

If you have need to consume REST API Service and store result into SQL Server or any other RDBMS/FLAT File then you can check this SSIS JSON Source Connector. JSON Source Connector can deformalize your nested JSON (It also supports JSONPath filter expression).

Watch this video on YouTube

REST API Task – Ad-hoc web requests – Call REST API (POST, DELETE)

Watch this video on YouTube

HTTP GET Request using SSIS Web Service Task or JSON/XML Source

Calling REST API from SQL Server BI Stack usually requires programming skill. But in this section we will learn how easy it is to call RESTful Web Service using SSIS Web Service Task, JSON Source or XML Source (all Drag and drop).

In this example we will get JSON data from ODATA Web Service.

Download and Install SSIS PowerPack
Create new sample SSIS package in BIDS/SSDT
From SSIS Toolbox look for items starting with “ZS”. Drag and Drop [ZS Rest API Web Service Task] to Designer Surface
Configure HTTP Request properties as below
Configure HTTP Response properties as below
Run SSIS Package

Pass values in HTTP Headers

You can pass value in HTTP Headers using direct approach or dynamic approach. If you wish to pass value from SSIS variable then use variable placeholder e.g. {{User::varSomeVariable}}. You can also use variable format specifier e.g.

Format Date: {{User::varSomeVariable,yyyy-MM-dd}}
Encode into BASE64: {{User::varSomeVariable,BASE64ENC}}
Decode into plain text from BASE64: {{User::varSomeVariable,BASE64DEC}}

SSIS REST Api Task - HTTP POST, SSIS Call Web Service

Calling REST API in SSIS using REST API Task, Pass headers, Body, Url Parameters

SSIS REST Api Task – HTTP GET, Test SSIS Web Service Call, Pass Custom Header

SSIS REST Api Task - Validate Status Code, Content and Header

REST API response validation ( By status code, content, header)

Pass values in Query String dynamically

Most of web services usually accept parameters via query string. You can also use variable placeholders to make your URL with Querystring dynamic (e.g. https://mysite.com/orderservice/?startrow={{User::varStart}}&endrow={{User::varEnd}}

HTTP POST Request using SSIS Web Service Task or JSON/XML Source

When you select HTTP POST Method then by default content-type is set to application/x-www-form-urlencoded. With POST Method you can send POST data (When POST Method selected Data textbox becomes editable. You can also use variable place holders in POST data to make it dynamic.

POST data is usually in key/value format (e.g. user=abcd&pass=mypass123) but sometimes service required data in JSON format or XML format. In that case you can submit that way. Depending on service requirement you have to set additional headers to indicate content-type (Please refer your service documentation).

Uploading files using HTTP Multipart/form-data POST Request

Many API support uploading files along with your POST request. Refer this article to learn more about how to upload files using REST API calls.

Passing Credentials to your Web Service

If your service requires authentication then you have to make sure you pass required credentials in correct format. Below is list of some most common authentication techniques.

There are mainly three connections

Using HTTP Connection

HTTP Connection supports many ways to authenticate to your API. Here are some popular modes.

Basic Authentication (UserID and Password Approach)
Dynamic Token approach (Two step process – First get Token and then Call API (Pass token extracted in first step)
Static Token
HMAC / Hash Signature Authentication
SOAP WSS Security
Windows Authentication (NTLM)
Client Certificate

OAuth Connection

Many modern APIs now support OAuth Standard (i.e. v2.0, v1.0, v1.0a). ZappySys Offers out of the box support for this standard using OAuth Connection Manager

OAuth authorization
- Authorize Code Grant (3-legged – using Login Popup)
- Client Credentials Grant (Simple key / secret – No login Popup)
- Password Grant (Use of userid /password rather than only key/secret)

Basic Authentication – Set Authorization Header (Base64 Encoding)

HTTP Standard supports BASIC Authorization mode in which you can pass userid and password into BASE64 encoded string. Check this article for more inform HOW to pass BASE64 Encoded Authorization Header

Token based approach – such as OAuth

This is becoming most common approach using protocols such as OAuth where user first authenticate to service using AccountKey and SecretKey. Once authenticated you receive token which can be valid for certain duration (or infinite duration). After you receive token you can call services (each service call will include this token). Parsing token from intial response can be achieved through REST API Response Filter Expression (e.g. $.token) or use JSON Parser Task

OAuth Authorization

All REST API Tasks/Components in Zappysys SSIS PowerPack support OAuth Authorization. OAuth is getting popular and many bigger companies already adopted this standard (e.g. Facebook, Twitter, Google, Salesforce). With this method you can connect to REST API Service without storing your Userid/Password. Only first time you have to login to using your credentials to get initial Token and after that service will continue using Access Token rather than your UserID/Password. Once Token expires it can automatically renew. Check this for more information about using OAuth

Windows Authentication (NTLM)

By default ZappySys REST API Tasks/Components use your default credentials. You can also use HTTP Connection Manager with Use Credentials > Use Windows Authentication option. See this article

Pass Client Certificate

Some times you may have to pass client certificate along with your web request. Server check your certificate passed along with your request and if matched it proceeds with your request. here is the real-world use case How Azure Management Api uses Client Certificate.

ZappySys HTTP Connection Manager and OAuth Connection Manager both supports passing Client certificate.

Saving HTTP Web Service Response to File/Variable

If you wish to save response to file then goto response tab of REST API Task and check “Save Response” option and you can select save to file option from dropdown

Save HTTP Web Response Headers / StatusCode

If you wish to save response to file then goto response tab of REST API Task and check “Save Response” option and you can select save to file option from dropdown

HTTP Web Response Validation

REST API Task supports validating your response for certain header, status code or content value. See Validation tab for more information. using this feature you can throw error if certain header is missing from response or reject response if StatusCode is other than 200.

REST API Pagination (Loop through multiple requests)

Another popular concept in REST API is paging. If your response is large then often server returns you partial response and then you have to make sure to consume reaming data by requesting all next urls until last page is returned.

Read this article to learn more about rest api paging

HTTP Web Response Error Handing

REST API Task supports rich error handling. You can ignore certain errors and save error flag into variable based on any of the following criteria

Continue on any error
Continue on error with specific Response Code (e.g. 404)
Continue on error with specific string in message

Read/Write and Parse Cookies for HTTP Web Request/Response

Many times you have to supply authentication token via cookies or any other information needs to be passed via cookies then REST API Task has complete support to read/write or parse individual value out of cookie string. Look at Cookies tab for more information. Cokkies Mapping grid can help you to map cookie value to SSIS variable (Write Cookie value to variable).

Read this article to learn more about passing cookies with your web request

Changing Headers/Url or POST data Dynamically

Most of fields on REST API Task, JSON Source and XML Source support placeholders.

Use placeholder anywhere in the following fields using {{User::yourVariable}} format. If you edit value you may see Insert PlaceHolder option.

Url
Body
Headers

You may format datetime using special syntax like this {{User::myDate,yyyy-MM-dd HHmmss.fff}}

Extract single value from JSON/XML Web Response (e.g. Token)

If your web response is in JSON or XML format then you can filter it using Expression (e.g. JSONPath or XPath). Go to Response Tab and select Content Type Format from dropdown. Enter expression to select nested sub document or value from your response. If you want to save individual properties from response into multiple variables then use JSON Parser Task

Most common usecase is if you getting token from your JSON REST API service. Assume that Service sends you response in following format but you only care about access_token property from below response. In that case you can use JSON Path expression like this $.data.access_token

{
    data: {
        access_token: 111223344444,
        expires_in: 3600
    }
}

{

data: {

access_token: 111223344444,

expires_in: 3600

}

REST API Testing / Debugging Tools (3rd party)

Now let’s look at few popular tools which you will need to test / debug REST API calls during your development cycle.

Testing Rest API

There few ways to Test REST APIs and each tool has its pros and cons but most popular tool to test REST API is Postman so we will start from there

Postman – GUI
Post man offers excellent User interface to Test API. You can edit URL / Method / Parameters and add authentication using ease of use. See our article how to migrate Postman settings to zappysys UI. Many API Providers offer easy to use postman settings files for Testing API in post man. If your API offers such file simply import it in postman and start testing before you can use ZappySys UI.
SoapUI – GUI
SoapUI is another tool to test SOAP or REST APIs. They are primarily known of SOAP API Testing but in recent versions they have enhanced REST API Testing capability.
cURL – Command line
If you want to call / test API via command line then this is the most popular tool. Many API vendors use cURL syntax in their API documentation. Here is how to migrate cURL syntax to ZappySys UI.
Fiddler- GUI
Fiddler has limited capability to Test API call – See Replay in Composer option
ZappySys REST API Task
You can use ZappySys REST API Task to test API inside Visual Studio. Edit URL / Headers / Body etc and click Test Request Response option

Debugging REST API – Proxy

So far we saw client tools to test Request / Response for the URL you entered in the UI but what if you like to Sniff Requests sent by some applications for which you have no way to know what Request Data / URL /Body is sent behind the scene? What response its getting back and so on? Well not to worry… Telerik Fiddler is the most popular tool at your finger tips and its FREE.

Fiddler – HTTP / HTTPS Proxy / Debugger
WireShark – Low level Network Packet Sniffer

Conclusion

REST API is becoming more and more popular each day. With that Data integration from RESTful services going to be challenge. SSIS REST API Task and SSIS JSON Source Connector can solve some of those issues you may face with SSIS. Use JSON Source connector if you have need to Store data into SQL Table. Use REST API Task if you have need to make ad-hoc HTTP/REST request (such as get token, Delete Record etc). ZappySys REST API Components gives you total control on your REST API Integration challenges without learning programming language (e.g. Python, Ruby, C#, Java).