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

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

In this article, you will learn about many REST API Tips / Tools on the topic of how to call a REST API using the SSIS REST API Task, JSON Source, or XML Source. You will also learn how to perform HTTP GET and HTTP POST Requests without needing to know any programming languages (e.g., C#, Java, Python) – simply do a drag-and-drop operation in SSIS.

What is REST API / RESTful Web Service

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

So, in short highlights for REST Web Service

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

Tasks/Components in SSIS for Consuming RESTful API / WebService

	JSON Source (Read from REST API, JSON file or OData Service): Use this dataflow component when you have to fetch data from a REST API web service like a table. This component enables you to extract JSON data from web services and de-normalize nested structures, allowing you to save the data to a Relational database, such as SQL Server, or any other target (Oracle, FlatFile, Excel, MySQL). This component also supports reading local JSON files or direct JavaScript strings (Wildcard pattern support, e.g., c:\data\file*.json).
	Web API Destination (POST data to API URL): Use this data flow component when you need to load data into a target system via API calls (POST to URL). E.g., Loading Contacts into a Marketing System like MailChimp for email campaigns. Loading documents into CRM or document storage systems, e.g,. Couchbase or ElasticSearch.
	REST API Tak: Use this task when you don’t want to pull REST API data in tabular format but want to call the REST API for POST data to the server, DELETE data from the server, or things like download an HTML page, extract Authentication tokens, etc, where you are not necessarily dealing with data in tabular format. This task also allows you many other options, such as saving the RAW response into a variable or a file.
	XML Source (SOAP, File, REST): Use this dataflow component when you have to fetch data from an XML or SOAP web service and consume data like a table. This component allows you to extract data from a web service and save it to SQL Server or any other target (Oracle, FlatFile, Excel, MySQL). This component also supports reading local XML files or a direct XML string.
	CSV Source (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 a web service and save it 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 need to consume a REST API Service and store the result into SQL Server or any other RDBMS/FLAT File, then you can check this SSIS JSON Source. The JSON Source can deserialize your nested JSON (it also supports JSONPath filter expressions).

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 the REST API from the SQL Server BI Stack usually requires programming skills. However, in this section, we will learn how easy it is to call a RESTful Web Service using the SSIS Web Service Task, JSON Source, or XML Source (all of which are drag-and-drop components).

In this example, we will retrieve JSON data from an OData web service.

Download and Install SSIS PowerPack
Create a new sample SSIS package in BIDS/SSDT
From the 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 a value in HTTP Headers using a direct approach or a dynamic approach. If you wish to pass a value from an SSIS variable, then use a variable placeholder, e.g., {{User::varSomeVariable}}. You can also use a 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}}

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

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

Pass values in the Query String dynamically

Most web services usually accept parameters via the 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 the HTTP POST method, the content-type is set to application/x-www-form-urlencoded by default. With the POST method, you can send POST data. When the POST method is selected, the 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 the service requires data in JSON format or XML format. In that case, you can submit that way. Depending on the service requirements, you must set additional headers to indicate the content type (please refer to your service documentation).

Uploading files using HTTP Multipart/form-data POST Request

Many API support uploading files along with your POST request. Refer to 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, ensure that you pass the credentials needed in the correct format. Below is a list of some of the most common authentication techniques.

There are mainly three connections:

Using HTTP Connection

HTTP Connection supports multiple methods for authenticating 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 the token extracted in the 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)

The HTTP Standard supports BASIC Authorization mode, in which you can pass the username and password as a base64-encoded string. Check this article for more information on how to pass a BASE64 Encoded Authorization Header

Token-based approach – such as OAuth

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

OAuth Authorization

All REST API Tasks/Components in Zappysys SSIS PowerPack support OAuth Authorization. OAuth is gaining popularity, and many larger companies have already adopted this standard (e.g., Facebook, Twitter, Google, Salesforce). With this method, you can connect to the REST API Service without storing your User ID/Password. Only the first time, you need to log in using your credentials to obtain an initial Token, and after that, the service will continue to use the Access Token rather than your username and password. Once the 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 the HTTP Connection Manager with the “Use Credentials”> “Use Windows Authentication” option. See this article

Pass Client Certificate

Sometimes you may have to pass a client certificate along with your web request. The server checks the certificate passed along with your request and, if it matches, 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 support passing a Client certificate.

Saving HTTP Web Service Response to a File or Variable

Let’s break down how to save the response from a ZappySys REST API Task into either an SSIS variable or a file within your SSIS package.

Scenario: Imagine you’re using the ZappySys REST API Task to retrieve JSON data from a URL. You want to store this data for further processing within your SSIS package.

1. Saving the REST API Response to an SSIS Variable

This is ideal when you need to manipulate the data within your SSIS package.

Add a ZappySys REST API Task to your Control Flow:
Drag and drop the task onto your SSIS package’s Control Flow.
Configure the REST API Task:
- Set the Connection Property to your REST API Connection Manager. (Optional)
- Specify the URL of the API endpoint.
- Configure any necessary headers, query parameters, or request body.
- Go to the “Response Settings” tab.
Configure the Output Variable:
- Check-mark Save Response content
- Select Save Mode as “Save to Variable”
- Select or create an SSIS variable of type String. This variable, named User::WeatherResponse will store the REST API response.
Saving the REST API Response to an SSIS Variable

2. Saving the REST API Response to a File

This is useful for archiving the response or when you need to process the data outside of SSIS.

Add a ZappySys REST API Task to your Control Flow:
Drag and drop the task onto your SSIS package’s Control Flow.
Configure the REST API Task:
- Set the Connection Property to your REST API Connection Manager. (Optional)
- Specify the URL of the API endpoint.
- Configure any necessary headers, query parameters, or request body.
- Go to the “Response Settings” tab.
Configure the Output File:
- Check-mark Save Response content
- Select Save Mode as “Save to File”
- In the “Enter File Path” property, specify the path and filename where you want to save the REST API response. For example, if you are fetching JSON data from a product API, you can save it to the following file: C:\Data\Products.json.

Saving the REST API Response to a File

REST APIs enable array filtering to extract specific data from JSON or XML responses. This is achieved through techniques such as query parameters, JSONPath (for JSON), and XPath (for XML), allowing for the precise selection of elements. Additionally, regular expressions can be employed for string-based filtering and data validation.

REST APIs enable array filtering to extract specific data from JSON or XML responses.

Key Setting: “Treat response as binary”

As highlighted in the steps, the “Treat response as binary” option in the “Response” tab is the most important setting for correctly saving binary files. If you don’t check this, the task might try to interpret the binary data as text.

Example Scenario:

Let’s say you want to download a ZIP file from a REST API or a Public URL:

API URL: https://example.com/api/download/archive.zip
File Path: C:\Downloads\archive.zip
In the ZS Rest API Task’s “Response” tab, you would:
- Check-mark Save Response content
- Select Save Mode as “Save to File”
- Check “Treat response as binary”.
- In the “Enter File Path” property, specify the path and filename where you want to save the REST API response. C:\Downloads\archive.zip.
REST API Task – Download File

By following these steps, you can effectively use the ZappySys Rest API Task to download and save binary content from REST APIs to files within your SSIS workflows.

Save HTTP Web Response Headers / StatusCode

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

HTTP Web Response Validation

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

REST API Pagination (Loop through multiple requests)

Another popular concept in REST API is paging. If your response is significant, the server often returns a partial response. You must then ensure that you consume the remaining data by requesting all subsequent URLs until the last page is returned.

Read this article to learn more about rest api paging

HTTP Web Response Error Handling

REST API Task supports rich error handling. You can ignore specific errors and save the error flag into a 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 a specific string in the message

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

Many times, you need to supply an authentication token via cookies, or any other information must be passed via cookies. In such cases, the REST API Task provides complete support for reading, writing, or parsing individual values from a cookie string. Look at the Cookies tab for more information. The Cookie Mapping grid can help you to map a cookie value to an SSIS variable (Write Cookie value to a variable).

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

Changing Headers/URL or POST data Dynamically

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

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

Url
Body
Headers

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

Extract a 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 the Response Tab and select Content Type Format from the dropdown. Enter an expression to select a nested sub-document or a value from your response. If you want to save individual properties from the response into multiple variables, then use JSON Parser Task

The most common use case is if you are getting a token from your JSON REST API service. Assume that Service sends you a response in the following format, but you only care about the access_token property from the response below. 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 a few popular tools that you will need to test/debug REST API calls during your development cycle.

Testing Rest API

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

Postman – GUI
Postman offers an excellent User interface to test the API. You can edit URLs, methods, and parameters, and add authentication with ease. See our article on how to migrate Postman settings to zappysys UI. Many API Providers offer easy-to-use Postman settings files for Testing API in Postman. If your API offers such a file, simply import it into Postman and start testing before using the ZappySys UI.
SoapUI – GUI
SoapUI is another tool to test SOAP or REST APIs. They are primarily known for SOAP API Testing, but in recent versions, they have enhanced REST API Testing capability.
cURL – Command line
If you want to call/test the API via the 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 the ZappySys REST API Task to test the API inside Visual Studio. Edit URL / Headers / Body, etc, and click the Test Request Response option

Debugging REST API – Proxy

So far, we have seen client tools to test Request/Response for the URL you entered in the UI, but what if you want to Sniff Requests sent by some applications for which you have no way to know what Request Data / URL, or Body is sent behind the scenes? What response is it getting back and so on? Well, not to worry… Telerik Fiddler is the most popular tool at your fingertips and it’s FREE.

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

Conclusion

The REST API is becoming increasingly popular every day. With that, Data integration from RESTful services is going to be a challenge. The SSIS REST API Task and SSIS JSON Source can help resolve some of the issues you may encounter with SSIS. Use JSON Source if you need to store data into a SQL Table. Use REST API Task if you need to make an ad-hoc HTTP/REST request (such as get token, Delete Record, etc). ZappySys REST API Components gives you total control over your REST API Integration challenges without learning a programming language (e.g., Python, Ruby, C#, Java).