Introduction
Often, REST API documentation uses example syntax for the curl command-line tool because it’s one of the most popular tools for making API calls. However, suppose you are an SSIS developer using ZappySys API Drivers/Connectors from either the SSIS PowerPack or the ODBC PowerPack. In that case, this blog post will help you translate the curl command line syntax to similar options available in the ZappySys Product. For SSIS PowerPack products, you can refer to Connectors/Tasks such as 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 the concepts are the same when you use other products, such as ODBC PowerPack JSON Driver / REST or XML Driver.
If you are using Postman to call the API, then refer to this article
If you are using Fiddler to call an 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 the HTTP Connection Manager to enable Basic Auth, then supply the username and password. |
| -H “name: value”, –header “name: value” | Supply HTTP Header(s) | Click on the 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 the Request Data text box and supply Body. If you need to pass content in JSON / XML, then select the 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 a file. | Click Edit next to the Request Data text box and supply Body. If you need to pass content in JSON / XML, select the Request ContentType dropdown as shown. To upload content stored from a local file, use this option. |
| -F key=value, –form key=value | Supply Content-Type multipart/form-data according to RFC 2388 | Enable the Multipart option for Request on the ZappySys UI, like this |
| -k, –insecure | Ignore SSL Certificate-related errors (e.g., allow expired certificate or untrusted certificate) | Go to the Advanced Tab and check the ” ignore SSL Related Errors option like this. |
| -x, –proxy [protocol://]host[:port] | Specify Proxy Settings | You can enable System default proxy or a custom proxy using the Proxy Tab in the 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
|
1 |
curl POST -u myusername:mypassword -H "Accept: application/json" -H "AppVersion:1.0" "https://mysite/v1/api/customers/" -d "customerid=AAA&OrderDate=2016-01-01" |
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 a URL using SSIS
Here is the screenshot of HTTP POST in JSON format using the 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
If you wish to POST data and parse the JSON response into rows, you can use the SSIS JSON Source. JSON Source has a powerful JSON Parser that parses large JSON (stored in a file, an API URL, or a Direct string) into Rows and Columns. This is useful if you wish to save the response into a target such as SQL Server or a CSV file.
JSON Source example
HTTP Headers – Convert Curl Syntax to SSIS
Set up HTTP headers in ZappySys Task/Connect, or click on Raw Edit or Add/Edit items in Grid.
HTTP Credentials – Convert Curl Syntax to SSIS
Here is how you pass credentials in the curl command line.
|
1 |
curl.exe https://myserver.com/api -u myusername:mypassword |
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)
|
1 |
curl.exe -x 192.168.2.1:8888 https://myserver.com/api |
Convert Curl Syntax to SSIS – Client Certificate Settings
HTTP Connection Manager UI
Passing client certificate along with Web request (i.e. pfx/pkcs12/p12)
Passing client certificate from file path (i.e. pfx/pkcs12/p12)
