- 1 Introduction
- 2 CheatSheet – Curl to SSIS PowerPack / ODBC PowerPack
- 3 Convert Curl Syntax to SSIS PowerPack – REST API Task / JSON / XML Connector
- 4 Convert Curl Syntax to SSIS – Client Certificate Settings
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.
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.|
|-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
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 URL using SSIS
Here is the screenshot of HTTP POST in JSON format using ZappySys REST API Task
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.
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.
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)
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)
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)