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

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.

Prerequisite

1. First, you will need to have SSIS installed
2. Secondly, make sure to have SSDT
3. Finally, do not forget to install ZappySys SSIS PowerPack

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.

Step-1: Get Dynamic Token (Configure ZS-HTTP Connection)

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:

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

   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.
   

   List of SSIS Connection Managers – Select HTTP Connection Manager

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

4. 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 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)
   2. Request Method as POST
   3. Request Body as below.
      
      XHTML

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

      1 2 3 4 5 6 7 8

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

   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.
      

      SSIS ZS HTTP Connection Manager: Dynamic Token Tab Configurations Settings of Request Tab

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

6. That’s it click on OK button to save your Connection Manager Configurations.

Now we have successfully configured the HTTP Settings for a Dynamic Token. Let´s start with an example. In this example, we will show how to read response data from SOAP / REST API with SSIS.

Step-2: Call SOAP API in SSIS (Pass API Token) in SSIS

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.
2. Now, Drag and Drop SSIS ZS XML Source Component from the SSIS Toolbox.
   

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

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

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

   1 2 3 4 5

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

   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.
   

   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"
   

   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:
   
   $.soapenv:Envelope.soapenv:Body.urn:Row[*]

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

   

   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.
   

   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 ZS XML 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.