HTTP POST in SSIS – Send data to Web API url

Introduction

In our previous article we saw how to call REST API or SOAP Web Service in SSIS. In this article we will only focus how to perform HTTP POST in SSIS using RESTful approach.

HTTP Protocol supports many methods also referred as verb (e.g. GET, PUT, POST, HEAD, DELETE). Based on this verb, target server (API server) can perform desired action. For example GET is usually used for read only data fetch (similar to select query in SQL). POST is usually used to create new record or update existing data.

For any HTTP operation you can submit parameters/data in 3 ways.

via URL query string
via HTTP Headers
via Body (Only valid for operations such as POST, PUT)

HTTP POST in SSIS

Traditionally whenever SSIS developers wanted to perform HTTP POST operation in SSIS they had to write C# Code using Script Task. This is no more case after ZappySys developed easy to use drag and drop REST API Tasks and Connectors. As of now there 4 main connectors/tasks which supports HTTP POST

1. SSIS Web API Destination
2. SSIS REST API Task
3. SSIS JSON Source
4. SSIS XML Source

SSIS PowerPack offers following two methods to POST data from your database to target API URL (e.g. SQL Server to API POST in JSON format).

Method-1 : HTTP POST using Web API destination (In Data Flow)

This method allows you to read data from any source and generate POST data and then submit to API endpoint using SSIS Web API Destination. Using this destination you can configure URL and API Body (e.g. JSON/XML document you want to submit) for each request. Check following screenshot to get idea how API POST works

Step-By-Step : Video Tutorial

You can also extract http response (i.e. JSON or XML) from Web API Destination and parse it into Rows and Columns using JSON Parser Transform like below. If you want to handle errors gracefully without stopping the execution then use error output (Drag red arrow from Web API destination and attach to downstream (Make sure you set Redirect for Error and Truncate both when prompted on attach)

Method-2 : HTTP POST using SSIS REST API Task (In Control Flow)

REST API Task support many advanced options too incase you want to tweak certain aspects of your POST request.

Here is the screenshot of HTTP POST in JSON format using ZappySys REST API Task

Parse response into rows/columns using SSIS JSON Source

If you wish to POST data and parse JSON response into rows/columns then you can use SSIS JSON Source for this purpose. JSON Source has great JSON Parser which supports parsing very large JSON  (stored in File or API URL or Direct string) into Rows and Columns. This is useful if you wish to save response into target such as SQL Server or CSV file.

Parse response into rows/columns using SSIS XML Source

If you wish to POST data and parse XML response into rows/columns then you can use SSIS XML Source for this purpose. XML Source supports parsing XML data (stored in File or API URL or Direct string) into Rows and Columns. This is useful if you wish to save response into target such as SQL Server or CSV file.

Passing credentials for Web Request for HTTP POST

Unfortunately there is no one standard for passing credentials to server but good thing is ZappySys REST API Task comes with many options to secure your request and pass credentials (Userid/Password or API Key or OAuth Token).

Basic Authentication

Most common approach of passing credentials is userid/password. Check this article how to use Url from connection if you wish to pass credentials using Basic authentication (i.e. Userid/password).

OAuth Authentication (1.0 or 2.0)

If your API support OAuth protocol then check these articles how to use OAuth with ZappySys REST connectors

Passing Parameters in POST Body (Make it dynamic)

SSIS PowerPack support variable placeholders for many editable text fields (usually indicated by variable icon or pencil icon).

If you wish to pass parameters in your data used in Body then use variable placeholder (e.g. {{User::varMyDate}}. When you edit POST Body you will see <<Insert Variable>> option.

Here is the complete list of available placeholder functions

Upload file using Multi-Part POST

If you have need to submit data to server from file then check this link (its shows few ways to upload file using HTTP POST)

Most common Content-Type for POST operation

There are many data formats which you can submit to server using HTTP POST but below is the most common data formats you will ever use for most of scenarios.

Data format is usually determined by server based on Content-Type header you include along with your request.

HTTP POST Form data – submit key/value pairs (application/x-www-form-urlencoded)

This is the most common data format for HTTP POST. Its also famous because any type you submit form on any website, most likely it will use below method for submitting your data to server. As you see its key/value pairs separated by ampersand [&]. Also if some special character found then it needs to be URL encoded (e.g. @ is replaced by %40)

HTTP POST JSON data (application/json content-type)

JSON is becoming very popular data format so if you are dealing with newer API then most likely you will have to submit your POST request using this content type like below. Assume that you creating new customer record then your request may look like below.

HTTP POST CSV data (text/plain content-type)

When API POST required data in CSV format then you have to specify this content type. Below is the example of HTTP POST with CSV data.

HTTP POST XML data (application/xml content-type)

When API POST required data in XML format then you have to specify (application/xml) content type. Below is the example of HTTP POST with XML data.

HTTP POST Binary data (Upload file)

If you are POSTing large amount of data from file (i.e. uploading file) to your API endpoint then check this article about How to Upload file using Multi-Part Upload

HTTP POST Raw data (no content type)

Do not pass any content-type header to send raw request

Advanced Read / Write Scenarios / API Error Handling

Now let’s look at some advanced scenario where ýou first have to call one API (GET or POST) using JSON Source or XML Source and then POST data to some other URL using Web API Destination.

Once you get response after POST/GET parse those records for further processing along with error handling. Such API calls can be achieved using following pattern.

Using Template Transform to build JSON or XML for POST Body

Check this article to learn more about some techniques to build JSON or XML for API POST Call (e.g. SOAP )

Encoding for POST Body

When using application/x-www-form-urlencoded content type (POST Form data) your server may require you to encode certain characters in property name or value using URL Encoding technique. As you see below key/value pairs separated by ampersand [&]. Also if some special character found then it needs to be URL encoded (e.g. @ is replaced by %40). There are many online tools found to encode or decode data using URL encoding technique.

Troubleshooting / Debugging HTTP POST issues using Fiddler

If you wish to see what’s being passed to server via any client tool or when you perform POST request ? Use tool called fiddler. Check this article to learn how to use Fiddler for debugging purpose.

Conclusion

As we enter into cloud first world you will face more and more need to integrate various RESTful API into your ETL workflows. SSIS PowerPack comes with complete toolset to automate any API calls (Read, Write or Parse). Try it for free here to discover many hidden features not discussed in this article.