- 1 Introduction
- 2 Prerequisites
- 3 Getting Started
- 4 Create HTTP Connection for Dynamic Token
- 5 Configure HTTP Connection Credential Type
- 6 Configure Request Settings
- 7 Configure Response Settings
- 8 Call API in SSIS with Dynamic API Token
- 9 Conclusion
- 10 References
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.
PrerequisitesBefore 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
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.
- 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.
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)
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:
- Request Method as POST
- Request Body as below. Notice we used
[$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
XHTML12345678<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
- Token/API Login URL
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
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']
- 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.
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.
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).
- Let’s double-click on XML Source Component to configure it’s Properties. Enter Request URL.
- Check Use Credentials (Username/Password OR Token), Select HTTP Connection Manager from the drop-down list, we have created just before.
- 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
12345<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. By default it sends Token in Authorization header if you dont do anything.
- 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 Filter Path: set as per your requirements. here for this example, we are setting:
- Check Include Parent Columns checkbox, for including parent Columns, and then click on Preview button to 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.
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.
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.