How to call SOAP / REST API using Dynamic Token in SSIS / ODBC

Introduction

In this blog, we will learn how to call SOAP / REST API using Dynamic Token in SSIS (i.e. Two steps authentication approach – Fist Call Login API to get token and then call API).

In our previous blog post, we saw how to call Web API using some industry standards approaches, such as OAuth 2.0 Authorization or Basic Authorization. However, not all APIs follow the industry standard to authenticate API requests. After talking with many customers we noticed that very common non-standard technique to authenticate API is Dynamic Token Approach. In this approach first, you have to call Login request to get a temporary token (i.e. sessionid ). Once you get that temp token you can supply it along with your API requests. These temp token/session id usually expires in short time (i.e. expire in one hour or so).

Sounds complicated? Well not really if you use ZappySys SSIS PowerPack or ODBC Drivers for API. In this post, we will go through the steps, to authenticate using Dynamic Token approach. We will show you features of ZappySys SSIS HTTP Connection Manager. For example purpose, we will call XML SOAP API using SSIS XML Source Connector but same concepts can be applied to read from JSON API using SSIS JSON Source Connector or CSV API using CSV Source Connector.

So let’s get started.

Prerequisites

Before we perform steps listed in this article, you will need to make sure following prerequisites are met:

SSIS designer installed. Sometimes it is referred as BIDS or SSDT (download it from Microsoft site).
Basic knowledge of SSIS package development using Microsoft SQL Server Integration Services.
Make sure ZappySys SSIS PowerPack is installed (download it).
Optional (If you want to Deploy and Schedule ) - Deploy and Schedule SSIS Packages

Getting Started

In order to start, we will show several examples. ZappySys includes an SSIS XML Source Connector and SSIS JSON Source Connector that will help you to call SOAP / REST API, read various data from SOAP / REST API with SSIS.

To get token from login / authentication URL you have to gather a few pieces from your API documentation.

How to call Login API for your API – (This is not the same URL as Vendor Site Login Page, but its usually API Login endpoint e.g. https://somecompany/api/v2/login)
API Response Format (e.g. JSON, XML ,CSV or Random Text )

In our below example steps, we will use XML API which requires POST method first to pass Userid / password to authenticate. If Authentication is successful it may return a response in XML format which contains token.

In the next step, we will call other API (e.g. get-data) and pass token as Authorization Header. All these steps will be done automatically using ZappySys SSIS HTTP Connection Manager.

Create HTTP Connection for Dynamic Token

To get API response data using SOAP / REST API call, you need to get an API Token from your login request URL. I am using ZappySys sample SOAP API to show you for the Demo purpose. Follow the steps mentioned below to get API Token:

Create in SSIS

Right Click on Connection Managers Window, and one Context menu appears for creating a new connection manager.
Connection Manager Window: Create New Connection
Select ZS HTTP Connection Manager from the list of Connection Managers and, double-click on it or click on Add button to add a new Connection Manager. By Default, The created connection manager is of Package-Level. You can set Project Level Connection manager too If you want.
List of SSIS Connection Managers – Select HTTP Connection Manager

Create in ODBC

To use HTTP Connection in ODBC Drivers you can change connection Type to HTTP like below and it will show similar UI discussed in this article.

Configure HTTP Connection Credential Type

Once you add new SSIS ZS HTTP Connection Manager, Go to General Tab and Configure HTTP Connection Manager for Two Step Authorization Flow. Enter API URL, Login Credentials, and configure some basic settings for the same. We used Login URL as just example but you can enter any valid API URL endpoint here. Actual Login URL goes on Dynamic Token Tab (see next step)
SSIS ZS HTTP Connection Manager: General Tab Configurations Settings

Configure Request Settings

Once you enter Username / password details on General Tab now we need to setup some information like Login Request URL, Body, Headers. Lets look at few options.

Passing User ID /Password in Body

Go to Dynamic Token tab, Enter the following pieces.

Token/API Login URL
For our example enter API Login URL as:
https://zappysys.com/downloads/files/test/soap-login.aspx
Request Method as POST

Request Body as below. Notice we used [$userid$] and [$password$] placeholders. These are replaced automatically at runtime so you do not have to hard code in Body and keep it secure. (See General Tab to enter UserID/Password)
Pass User ID / Password in Body

<soapenv:Envelope xmlns:soapenv=""http://schemas.xmlsoap.org/soap/envelope/"" xmlns:urn=""urn:partner.soap.sforce.com"">
   <soapenv:Body>
      <urn:login>
         <urn:username>[$userid$]</urn:username>
         <urn:password>[$password$]</urn:password>
      </urn:login>
   </soapenv:Body>
</soapenv:Envelope>

<soapenv:Envelope xmlns:soapenv=""http://schemas.xmlsoap.org/soap/envelope/"" xmlns:urn=""urn:partner.soap.sforce.com"">

<soapenv:Body>

<urn:login>

<urn:username>[$userid$]</urn:username>

<urn:password>[$password$]</urn:password>

</urn:login>

</soapenv:Body>

</soapenv:Envelope>

Select Request Body Content Type. For JSON data it may be application/json. For Key/Value Pair key1=value1&key2=value2 leave it Default.Passing User ID /Password in Request BODY
SSIS ZS HTTP Connection Manager: Dynamic Token Tab Configurations Settings of Request Tab

Passing User ID / Password in URL (GET Request)

Our Example API pass credentials in the POST body but some API may pass via URL as GET request.

In such case, you can enter like this
https://mysite.com/api/v2/login?userid=[$userid$]&password=[$password$] Notice in above URL we used [$userid$] and [$password$] placeholders. These are replaced automatically at runtime (See General Tab to enter UserID/Password)

Passing User ID /Password in Header as Basic Authentication

Another scenario is API requires HTTP Basic authentication, (Pass User ID / Password in Header as Base64 encoded value) then you can pass credentials like this:

Authorization: Basic <<[$userid$]:[$password$],FUN_BASE64ENC>>

Here we have used FUN_BASE64ENC function but there are many other functions can be used anywhere in Body or Headers

Using Basic Authentication in Dynamic Token approach

Configure Response Settings

Now lets configure how to extract token (single value) from response. Which can be XML, JSON or sometimes Raw Text. We support 4 modes (default = None means full response text is treated as token). If you want to extract just single value and not full text then use below settings.

XML Expression Type (Use XPath)

Now, Go to Response Settings tab under a Dynamic Token tab, Enter Response Settings like Token Extract Mode, Expression Type, Content Filter Expression as below XPath Expression. The expression basically extracts content from sessionid XML node using below XPath Expression. //*[local-name()='sessionid']
SSIS ZS HTTP Connection Manager: Dynamic Token Tab Configurations Settings of Response Tab
That’s it click on OK button to save your Connection Manager Configurations.

JSON Expression Type (Use JSONPath)

If you API returns JSON response (like Walmart API Example) then you can do something like below. Choose Expression Type=JSON and in Response Content Filter You can enter expression like $.some_property_name (e.g. $.access_token). Check more about How to write JSON Path expression here.

Walmart API – Extract Token – Response Settings

Regular Expression Type (Use Regex)

Another advanced mode to extract token from response is Regex Mode. You can write any valid regular expression like this one to extract just one value.

For example if your response returns raw string with token in double quotes but you want just token and no double quotes around it then try something like below.

Extract Token from response – Remove double quotes around value using Regular Expression

Call API in SSIS with Dynamic API Token

Now once you have setup HTTP connection manager to authenticate and get API token, we are ready for next step. Now we can use that token in SOAP / REST API Service call. So, let’s move to the SSIS Part.

Once SSIS Package is open, Goto Data Flow Section, For that Drag and Drop DataFlow Task from the SSIS Toolbox.
Now, Drag and Drop SSIS ZS XML Source Component from the SSIS Toolbox (if your API is JSON based then drag ZS JSON Source).
Drag and Drop ZS SSIS XML Source Component
Let’s double-click on XML Source Component to configure it’s Properties. Enter Request URL.

https://zappysys.com/downloads/files/test/soap-getdata.aspx

1

https://zappysys.com/downloads/files/test/soap-getdata.aspx

SSIS XML Source: Enter your SOAP Request URL
Check Use Credentials (Username/Password OR Token), Select HTTP Connection Manager from the drop-down list, we have created just before.
ZS SSIS XML Source Component: Check Use Credentials and Select Connection Manager

Select the Request Method and Enter SOAP Request Body something like this. Notice that we used [$token$] as a placeholder. This is automatically replaced at runtime by ZS-HTTP Connection Manager.Passing Token in Body

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:urn="urn:partner.soap.sforce.com">
   <soapenv:Body>
      <urn:sessionid>[$token$]</urn:sessionid>
   </soapenv:Body>
</soapenv:Envelope>

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:urn="urn:partner.soap.sforce.com">

<soapenv:Body>

<urn:sessionid>[$token$]</urn:sessionid>

</soapenv:Body>

</soapenv:Envelope>

Passing Token in Header

If your API expects token via HTTP Header (e.g. Authorization: xxxx-my-token-xxxxxxxx ) then make sure you configure Auth Scheme and Header name on HTTP Connection. By default it sends Token in Authorization header if you dont do anything.

ZS SSIS XML Source Component: Enter SOAP Request Body

Select Content-Type as per your Request, here we are selecting text/XML;charset=UTF-8 and adding 1 HTTP Header: SOAPAction: "https://zappysys.com/soap-getdata"
ZS SSIS XML Source Component: Select Content-Type Add HTTP Request Header
select Filter Path: set as per your requirements. here for this example, we are setting:

$.soapenv:Envelope.soapenv:Body.urn:Row[*]

1

$.soapenv:Envelope.soapenv:Body.urn:Row[*]

ZS SSIS XML Source Component: Select Filter
Check Include Parent Columns checkbox, for including parent Columns, and then click on Preview button to see the Preview data.
SSIS XML Source Component: See the Preview data
That’s it. Click on OK button to Save your Configuration.
Finally drag any destination (i.e. OLEDB Destination) from your SSIS toolbox. If you want to load this data into SQL, then Drag OLE DB Destination Component, and configure it.

Conclusion

In this blog, we learned how to call any SOAP/REST API with Dynamic Token, retrieve Service response data with SSIS using XML Source Component OR JSON Source Component in a very simple way. Furthermore, you can achieve many more functionalities with this tool. Check our blogs/articles on XML Source Component to find out what this tool is capable of more.

References

Finally, you can use the following links for more information about the call any SOAP / REST API with Dynamic Token feature with our tools:

Landing Page for XML Source, you can also find Tutorial Video here.
Help File: Documentation of XML Source.

How to call SOAP / REST API using Dynamic Token in SSIS / ODBC

Introduction

Prerequisites

Getting Started