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

Posted on by
Contents hide
1 Introduction
2 Prerequisites
3 Getting Started
4 Create HTTP Connection for Dynamic Token
4.1 Create in SSIS
4.2 Create in ODBC
5 Configure HTTP Connection Credential Type
6 Configure Request Settings
6.1 Passing User ID /Password in Body
6.2 Passing User ID / Password in URL (GET Request)
6.3 Passing User ID /Password in Header as Basic Authentication
7 Configure Response Settings
7.1 XML Expression Type (Use XPath)
7.2 JSON Expression Type (Use JSONPath)
7.3 Regular Expression Type (Use Regex)
8 Call API in SSIS with Dynamic API Token
9 Conclusion
10 References

Introduction

Logo REST APIIn 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 the steps listed in this article, you will need to make sure the following prerequisites are met:
  1. SSIS designer installed. Sometimes it is referred to as BIDS or SSDT (download it from the Microsoft site).
  2. Basic knowledge of SSIS package development using Microsoft SQL Server Integration Services.
  3. Make sure ZappySys SSIS PowerPack is installed (download it, if you haven't already).
  4. (Optional step). Read this article, if you are planning to deploy packages to a server and schedule their execution later.

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

  1. Right Click on Connection Managers Window, and one Context menu appears for creating a new connection manager.
    Select new connection

    Connection Manager Window: Create New Connection

  2. 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.
    Connection list from SSIS

    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.

New HTTP connection in ODBC

Create an HTTP connection inside an ODBC driver

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)

HTTP connection with dynamic token in ODBC

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

  1. Go to Dynamic Token tab, Enter the following pieces.
    1. Token/API Login URL
      For our example enter API Login URL as:
      https://zappysys.com/downloads/files/test/soap-login.aspx
    2. Request Method as POST
    3. 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
    4. 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
      HTTP connection dynamic tab

      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

HTTP connection dynamic token in header request credentials

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)

  1. 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']
    Get XML response for dynamic token

    ODBC ZS HTTP Connection Manager: Dynamic Token Tab Configurations Settings of Response Tab

  2. 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

SSIS ZS HTTP Connection Manager – Get a JSON response for the token

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.

Dynamic token from a regex expression

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.

  1. Once SSIS Package is open, Goto Data Flow Section, For that Drag and Drop DataFlow Task from the SSIS Toolbox.
    Use data flow

    Drag and drop Data flow task

  2. 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

    Drag and Drop ZS SSIS XML Source Component

  3. Let’s double-click on XML Source Component to configure it’s Properties. Enter Request URL.
    Enter URL to XML source

    SSIS XML Source: Enter your SOAP Request URL

  4. Check Use Credentials (Username/Password OR Token), Select HTTP Connection Manager from the drop-down list, we have created just before.
    USe a credentials inside XML source

    ZS SSIS XML Source Component: Check Use Credentials and Select Connection Manager

  5. 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
    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.
    Dynamic Token in the header

    ZS SSIS XML Source Component: Enter SOAP Request Body

  6. 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"
    Select Content-Type Add HTTP Request Header

    ZS SSIS XML Source Component: Select Content-Type Add HTTP Request Header

  7. select Filter Path: set as per your requirements. here for this example, we are setting:
    XML source filter with dynamic token

    ZS SSIS XML Source Component: Select Filter

     

  8. Check Include Parent Columns checkbox, for including parent Columns, and then click on Preview button to see the Preview data.
    Preview data XML source dynamic token

    SSIS XML Source Component: See the Preview data

  9. That’s it. Click on OK button to Save your Configuration.
  10. 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:

Posted in HTTP Connection, SSIS XML Source (File / SOAP) and tagged , , , , .