![\"Logo](\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/REST-API-icon.jpg\"){kind=icon}
<\/a>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).<\/p>\nIn our previous blog post, we saw how to call Web API using some industry standards approaches, such as OAuth 2.0 Authorization<\/a> or Basic Authorization<\/a>. 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).<\/p>\n Sounds complicated? Well not really if you use ZappySys SSIS PowerPack<\/a> or ODBC Drivers for API<\/a>. In this\u00a0post, we will go through the steps, to authenticate using Dynamic Token approach. We will show you features of\u00a0\u00a0ZappySys SSIS HTTP Connection Manager<\/a>. For example purpose, we will call XML SOAP API using SSIS XML Source Connector<\/a> but same concepts can be applied to read from JSON API using\u00a0\u00a0SSIS JSON Source Connector<\/a>\u00a0or CSV API using CSV Source Connector<\/a>.<\/p>\n So let’s get started.<\/p>\n In order to start, we will show several examples. ZappySys includes an\u00a0SSIS XML Source Connector<\/a> and\u00a0\u00a0SSIS JSON Source Connector<\/a> that will help you to call SOAP \/ REST API, read various data from SOAP \/ REST API with SSIS.<\/p>\n To get token from login \/ authentication URL you have to gather a few pieces from your API documentation.<\/p>\n 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.<\/p>\n 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\u00a0 ZappySys SSIS HTTP Connection Manager<\/a>.<\/p>\n To get API response data using SOAP \/ REST API call, you\u00a0need\u00a0to get an API Token from your\u00a0login 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:<\/p>\n Connection Manager Window: Create New Connection<\/p><\/div>\n List of SSIS Connection Managers \u2013 Select HTTP Connection Manager<\/p><\/div><\/li>\n<\/ol>\n 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.<\/p>\n Create an HTTP connection inside an ODBC driver<\/p><\/div>\n 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)<\/p>\n SSIS ZS HTTP Connection Manager: General Tab Configurations Settings<\/p><\/div>\n 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.<\/p>\n SSIS ZS HTTP Connection Manager: Dynamic Token Tab Configurations Settings of Request Tab<\/p><\/div><\/li>\n<\/ol>\n<\/li>\n<\/ol>\n Our Example API pass credentials in the POST<\/strong> body but some API may pass via URL as GET<\/strong> request.<\/p>\n In such case, you can enter like this Another scenario is API requires HTTP Basic authentication<\/a>, (Pass User ID \/ Password in Header as Base64 encoded value) then you can pass credentials like this:<\/p>\n Here we have used\u00a0 Using Basic Authentication in Dynamic Token approach<\/p><\/div>\n 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.<\/p>\n ODBC ZS HTTP Connection Manager: Dynamic Token Tab Configurations Settings of Response Tab<\/p><\/div><\/li>\n If you API returns JSON response (like Walmart API Example<\/a>) then you can do something like below. Choose Expression Type=JSON and in Response Content Filter You can enter expression like $.some_property_name\u00a0 (e.g. $.access_token). Check more about How to write JSON Path expression here<\/a>.<\/p>\n SSIS ZS HTTP Connection Manager – Get a JSON response for the token<\/p><\/div>\n Another advanced mode to extract token from response is Regex Mode. You can write any valid regular expression like this one<\/a> to extract just one value.<\/p>\n 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.<\/p>\n Extract Token from response \u2013 Remove double quotes around value using Regular Expression<\/p><\/div>\n 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\u00a0move to the SSIS Part.<\/p>\n Drag and drop Data flow task<\/p><\/div><\/li>\n Drag and Drop ZS SSIS XML Source Component<\/p><\/div><\/li>\n SSIS XML Source: Enter your SOAP Request URL<\/p><\/div><\/li>\n ZS SSIS XML Source Component: Check Use Credentials and Select Connection Manager<\/p><\/div><\/li>\n Passing Token in Header ZS SSIS XML Source Component: Enter SOAP Request Body<\/p><\/div><\/li>\n ZS SSIS XML Source Component: Select Content-Type Add HTTP Request Header<\/p><\/div><\/li>\n ZS SSIS XML Source Component: Select Filter<\/p><\/div>\n <\/li>\n SSIS XML Source Component: See the Preview data<\/p><\/div><\/li>\n In this blog, we learned how to call any SOAP\/REST API with Dynamic Token, retrieve Service response data with SSIS using\u00a0XML Source\u00a0Component<\/u><\/span><\/a>\u00a0OR\u00a0JSON Source Component<\/a>\u00a0 \u00a0in a very simple way. Furthermore, you can achieve many more functionalities with this tool. Check our blogs\/articles on\u00a0XML Source Component\u00a0<\/strong>to find out what\u00a0this tool<\/em>\u00a0is capable of more.<\/p>\n Finally, you can use the following links for more information about the call any SOAP \/ REST API with Dynamic Token feature with our tools:<\/p>\n 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 […]<\/p>\n","protected":false},"author":6,"featured_media":4254,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[355,62],"tags":[18,161,3,12,143],"yoast_head":"\r\n<\/h2>\n
Prerequisites<\/span><\/h2>\r\nBefore we perform the steps listed in this article, you will need to make sure the following prerequisites are met:\r\n
\r\n \t
Getting Started<\/h2>\n
\n
Create HTTP Connection for Dynamic Token<\/h2>\n
Create in SSIS<\/h3>\n
\n
<\/a><\/a>Create in ODBC<\/h3>\n
<\/a>Configure HTTP Connection Credential Type<\/h2>\n
<\/a>Configure Request Settings<\/h2>\n
Passing User ID \/Password in Body<\/strong><\/h3>\n
\n
\n
\nFor our example enter API Login URL as:
\nhttps:\/\/zappysys.com\/downloads\/files\/test\/soap-login.aspx<\/span><\/li>\n
\nPass User ID \/ Password in Body<\/strong><\/p>\n<soapenv:Envelope xmlns:soapenv=\"\"http:\/\/schemas.xmlsoap.org\/soap\/envelope\/\"\" xmlns:urn=\"\"urn:partner.soap.sforce.com\"\">\r\n <soapenv:Body>\r\n <urn:login>\r\n <urn:username>[$userid$]<\/urn:username>\r\n <urn:password>[$password$]<\/urn:password>\r\n <\/urn:login>\r\n <\/soapenv:Body>\r\n<\/soapenv:Envelope><\/pre>\n<\/li>\n
<\/a>Passing User ID \/ Password in URL (GET Request)<\/strong><\/h3>\n
\nhttps:\/\/mysite.com\/api\/v2\/login?userid=[$userid$]&password=[$password$]<\/span>
\nNotice in above URL we used\u00a0[$userid$]<\/span>\u00a0 and\u00a0[$password$]<\/span>\u00a0 \u00a0placeholders. These are replaced automatically at runtime (See General Tab to enter UserID\/Password)<\/p>\nPassing User ID \/Password in Header as Basic Authentication<\/strong><\/h3>\n
Authorization: Basic <<[$userid$]:[$password$],FUN_BASE64ENC>>
\n<\/code><\/p>\nFUN_BASE64ENC<\/code>\u00a0function but there are many other functions<\/a> can be used anywhere in Body or Headers<\/p>\n<\/a>Configure Response Settings<\/h2>\n
XML Expression Type (Use XPath)<\/h3>\n
\n
<\/a>JSON Expression Type (Use JSONPath)<\/h3>\n
<\/a>Regular Expression Type (Use Regex)<\/h3>\n
<\/a>Call API\u00a0<\/strong>in SSIS with\u00a0<\/strong>Dynamic API Token<\/strong><\/h2>\n
\n
<\/a><\/a>https:\/\/zappysys.com\/downloads\/files\/test\/soap-getdata.aspx<\/pre>\n
<\/a><\/a><soapenv:Envelope xmlns:soapenv=\"http:\/\/schemas.xmlsoap.org\/soap\/envelope\/\" xmlns:urn=\"urn:partner.soap.sforce.com\">\r\n <soapenv:Body>\r\n <urn:sessionid>[$token$]<\/urn:sessionid>\r\n <\/soapenv:Body>\r\n<\/soapenv:Envelope><\/pre>\n
\n<\/strong>
\nIf your API expects token via HTTP Header (e.g. Authorization:<\/strong> xxxx-my-token-xxxxxxxx<\/strong> ) 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.<\/p>\n<\/a><\/a>$.soapenv:Envelope.soapenv:Body.urn:Row[*]<\/pre>\n
<\/a><\/a>Conclusion<\/span><\/h2>\n
References<\/span><\/h2>\n
\n