Introduction
In the previous article, we learned how to copy settings from curl to ZappySys products, and in this article, we will continue on how to migrate Postman API call to SSIS / ODBC PowerPack. Postman is a very popular client tool to test API calls. Many API providers document how to call their API using Postman. In this article we will focus on how to use the same settings in ZappySys Products to implement API integration in SSIS or other apps like Power BI, Informatica, Excel, Tableau, SSRS… many ODBC apps.
When you are connecting to an API you may have examples of how to do that in Postman. If you know how to do it in Postman, you are one step away from making it work in SSIS PowerPack and ODBC PowerPack (JSON, XML, CSV Drivers, and other). Most of the examples will show how to do that specifically using ODBC PowerPack and JSON Driver, but since ODBC PowerPack UI is very similar to the connectors of SSIS PowerPack, it will be quite clear where to use it in SSIS PowerPack as well. In the examples where UI differs significantly, both – ODBC and SSIS PowerPack – examples will be presented.
Prerequisites
In this article we assume you meet these requirements:
When migrating Postman API call to SSIS PowerPack
- Postman installed.
- SSIS designer installed. Sometimes it is referred to as BIDS or SSDT (download it from Microsoft site).
- Basic knowledge of SSIS package development using Microsoft SQL Server Integration Services.
- ZappySys SSIS PowerPack is installed (download it).
When migrating Postman API call to to ODBC PowerPack
- Postman installed.
- ZappySys ODBC PowerPack installed (download it).
The Basics
This section shows how to migrate simple options such as URL, HTTP Request Method, and HTTP Headers.
How to configure the URL and HTTP Request Method
Migrate Postman API call to SSIS / ODBC (API URL setting to ODBC Driver (GET / POST) )
How to configure HTTP Headers
Migrate Postman HTTP Headers setting to ODBC Driver
Posting data
In this section, you will find how to post data in ZappySys products. You will learn how to post different formats of data, upload files and send multi-part data.
How to configure POST raw body
Migrate Postman Body / Content-Type setting to ODBC Driver
How to configure POST JSON body
Migrate Postman Body / Content-Type setting to ODBC Driver
Use this table to determine what kind of Body Content Type to configure:
| Data type | Body Content Type |
| Text | text/plain |
| JSON | application/json |
| HTML | text/html |
| XML | application/xml |
| You don’t know the type | Default (i.e. form/x-www-form-urlencoded data) |
How to configure POST form/x-www-form-urlencoded data
Migrate Postman Body (x-www-form-urlencoded) setting to ODBC Driver
How to configure multipart form POST data
Migrate Postman Multi-Part File Upload setting to ODBC Driver (Upload File)
Authentication
This section will cover all common authentication methods, starting from using API Key to OAuth 2.0.
How to authenticate using API Key in HTTP headers
Authentication – Migrate Postman API Key (via header) setting to ODBC Driver
How to authenticate using API Key as a parameter in URL
Authentication – Migrate Postman API Key (via URL query parameter) setting to ODBC Driver
How to authenticate using Bearer Token
Authentication – Migrate Postman Bearer Token setting to SSIS / ODBC Driver
How to authenticate using Basic Authentication
In ODBC PowerPack
Authentication – Migrate Postman Basic Auth setting to SSIS / ODBC Driver (Login using Userid / password)
In SSIS PowerPack
In SSIS PowerPack use the same configuration as above, just create ZS-HTTP Connection Manager using instructions below:
Authentication – Migrate Postman Basic Auth setting to SSIS JSON / XML Source (Login using Userid / password)
How to authenticate using OAuth
In ODBC PowerPack
- Configure main options:
Authentication – Migrate Postman OAuth setting to SSIS / ODBC Drivers ( JSON / XML / CSV API) – Client ID / Secret / Scope / Auth and Token Url (Client Credential Grant Type, Password Grant Type, Code Authorization Grant Type)
- Configure Callback/Return Url:
Authentication – Migrate Postman OAuth setting to SSIS / ODBC Drivers ( Callback / Redirect URL )
If you are using a known, public API service, you may try locating it in the OAuth Providers list, which will save you from configuring Authorization and Access Token URLs:
For simplicity and if your company policy allows it or at least for testing purposes, you can use a Default OAuth Application created by ZappySys:
In SSIS PowerPack
In SSIS PowerPack use the same configuration as above, just create ZS-OAUTH Connection Manager using instructions below:
Authentication – Migrate Postman OAuth setting to SSIS JSON / XML Source
How to authenticate using AWS Signature
In ODBC PowerPack
Authentication – Migrate Postman AWS API call setting to SSIS / ODBC Drivers ( JSON / XML / CSV API call)
In SSIS PowerPack
In SSIS PowerPack use the same configuration as above, just create ZS-OAUTH Connection Manager using instructions below:
How to authenticate using NTLM Authentication
In ODBC PowerPack
In SSIS PowerPack
In SSIS PowerPack use the same configuration as above, just create ZS-HTTP Connection Manager using instructions below:
Conclusion
This article was quite self-explanatory, but if you have any questions or you have an authentication method that is not listed here and you don’t know how to proceed, please, contact us and we will try to help you: https://zappysys.com/support/.