Convert CURL to SSIS PowerPack / ODBC for API Call

Introduction

Many time REST API documentation uses example syntax for CURL command line tool because its one of the most popular tool to make API call. However if you are a SSIS developer and using ZappySys API Drivers / Connectors from either SSIS PowerPack or ODBC PowerPack products then this blog post will help you to translate cUrl command line syntax to similar options available in ZappySys Product. For SSIS PowerPack products you can refer to Connectors / Tasks like ZappySys REST API Task / JSON Source Connector / XML Source. To make this article short and simple we will show many screenshots from SSIS PowerPack UI but concepts are same when you use other product such as ODBC PowerPack JSON Driver / REST  or XML Driver.

 

If you are using Postman to call API then refer to this article

If you are using Fiddler to call API then refer to this article

CheatSheet – Curl to SSIS PowerPack / ODBC PowerPack

Before we dig down more. Let’s look at how each command line parameter from CURL maps to SSIS PowerPack settings.

CURL Parameter (Short / Long) Purpose SSIS PowerPack Setting
method Pass HTTP method (e.g. GET, POST, PUT, DELETE) Select Method from Request Method Dropdown on  ZappySys REST API Task or JSON Source Connector or XML Source  like this
-u userid:password, –user userid:password  Authenticate using Basic Authentication Using UserID and Password Use HTTP Connection Manager and Enable Basic Auth like this way and supply userid and password.
-H “name: value”, –header “name: value”  Supply HTTP Header(s) Click on Raw Edit option or enter key value pairs of HTTP headers in the Headers grid  like this
-d “key=value”,  –data “key=value”
Supply BODY parameters using www-form-urlencoded Content Type Click Edit next to Request Data text box and supply Body. If you need to pass content in JSON / XML then select Request ContentType dropdown like this
-d “some data”,  –data “some data”
-d @filepath,  –data @filepath
Supply BODY parameters using Content-Type other than www-form-urlencoded. You can also supply content from file. Click Edit next to Request Data text box and supply Body. If you need to pass content in JSON / XML then select Request ContentType dropdown like this. To upload content stored from local file use this option.
-F key=value, –form key=value Supply Content-Type multipart/form-data according to RFC 2388 Enable Multi Part option for Request on ZappySys UI like this
-k, –insecure Ignore SSL Certificate related errors (e.g. allow expired certificate or untrusted certificate) Go to Advanced Tab and Check ignore SSL Related Errors option like this.
-x, –proxy [protocol://]host[:port] Specify Proxy Settings You can enable System default proxy or custom proxy by using Proxy Tab on OAuth / HTTP COnnection Manager. Check this.

Convert Curl Syntax to SSIS PowerPack – REST API Task / JSON / XML Connector

Take a look at the following example of CURL syntax

Where :

-u  or --user  = UserID and Password for server (This option sends credentials in BASIC Authentication mode)
-H  or --header  = HTTP Header (e.g. Key : Value)
-d  or --data  = Data for POST

For step by step information check Article Learn how to POST data to URL using SSIS

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

Convert CURL Command Line Syntax to SSIS - Perform HTTP POST operation using SSIS REST API Task - Send data in JSON content type

Convert CURL Command Line Syntax to SSIS – Perform HTTP POST operation using SSIS REST API Task – Send data in JSON content type

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.

Post JSON data to HTTP URL and parse response into columns and rows

HTTP Headers – Convert Curl Syntax to SSIS

To setup HTTP headers in ZappySys Task/Connector click on Raw Edit or Add/Edit items in Grid.

HTTP Credentials – Convert Curl Syntax to SSIS

Here is how you pass Credentials in cUrl command line.

To setup HTTP credentials in SSIS for Basic / NTFS or Kerberos use HTTP Connection Manager (Access Mode must be changed to Url from Connection on ZappySys REST API Task and for JSON Source Connector or XML Source  check Use Connection Option)

If you have OAuth 2.0 Credentials (Application ID, Application Secret) then use SSIS OAuth Connection Manager rather than HTTP Connection

Check this article to learn about how to pass HTTP Basic Credentials in SSIS PowerPack

Proxy Setting – Convert Curl Syntax to SSIS

Here is how you pass Proxy server info in cUrl command line ( -x  along with Proxy Server Host/IP and Port)

Convert Curl Syntax to SSIS – Client Certificate Settings

HTTP Connection Manager UI

HTTP Connection Manager UI

Passing client certificate along with Web request (i.e. pfx/pkcs12/p12)

Passing client certificate along with Web request (From Local/User store)

Passing client certificate from file path (i.e. pfx/pkcs12/p12)

Passing client certificate from file path (i.e. pfx/pkcs12/p12)

 

 

Posted in SSIS Tips & How-Tos and tagged , , , , , , , .