Call REST API using SSIS Web Service Task / JSON / XML SourceReach for the Clouds, Inc.Next Generation SSIS Tasks and Connectors Series

AUTHOR: NAYAN PATEL | SR. ETL SSIS ARCHITECTN PAT E L @ R FTC LO U D S . C O M

Content• Introduction• What is REST API / RESTful Web Service• Tasks/Components in SSIS for Consuming RESTful webservice• Using SSIS JSON Source to read from REST API and load into SQL Server• REST API Task – Ad-hoc web requests – Call REST API (POST, DELETE)• HTTP GET Request using SSIS Web Service Task or JSON/XML Source6.1 Pass values in HTTP Headers

Pass values in Query String dynamically• HTTP POST Request using SSIS Web Service Task or JSON/XML Source7.1 Uploading files using HTTP

Multipart/form-data POST Request• Passing Credentials to your Web Service8.1 Basic Authentication – Set Authorization Header (Base64 Encoding)

Token based approach – such as OAuthOAuth AuthorizationWindows Authentication (NTLM)Pass Client Certificate

• Saving HTTP Web Service Response to File/Variable• Save HTTP Web Response Headers / StatusCode• HTTP Web Response Validation• REST API Pagination (Loop through multiple requests)• HTTP Web Response Error Handing• Read/Write and Parse Cookies for HTTP Web Request/Response• Changing Headers/Url or POST data Dynamically• Extract single value from JSON/XML Web Response (e.g. Token)• Conclusion

Introduction

• In this article you will learn how to call REST API using SSIS Web Service Task , JON Source Connector or XML Source Connector. You will also learn how to perform HTTP GET Request and HTTP POST Request without knowing any programming languages (e.g. C#, JAVA, Python)………

Simply do Drag and Drop in SSIS.

Introduction

• In this article you will learn how to call REST API using SSIS Web Service Task , JON Source Connector or XML Source Connector. You will also learn how to perform HTTP GET Request and HTTP POST Request without knowing any programming languages (e.g. C#, JAVA, Python)………

Simply do Drag and Drop in SSIS.

What is REST API / RESTful Web Service

• So lets first understand What is REST API or sometimes referred as RESTful Web Service. This is new buzzword which you will hear a lot. More and more services are available in Cloud which makes it obvious to come up with some way so you can access data more firewall friendly manner. What can be better option than access it using several decade old HTTP protocol so no additional configuration required for anybody accessing Cloud Services? So in short highlights for REST Web Service

What is REST API / RESTful Web Service

• REST Web Service is stateless client-server service model• By passing HTTP verb you can perform server side action over

standard HTTP protocol (e.g. GET, POST, LIST, DELETE,HEAD)• You can pass parameters via URL query string and via HTTP

Headers• If you doing HTTP POST then you can pass additional data in

Request Body along with other two method described above• REST API works by sending HTTP Request and Getting HTTP

Response• HTTP Response of Web Service can contain Headers and Response

Data which can be Binary format, Text format, JSON, XML etc.• You can secure your data by simply sending it over HTTPS (Secure

HTTP) protocol or encrypting values passed along with your request

Tasks/Components in SSIS for Consuming RESTful webservice

• JSON Source Connector (REST, File, OData): Use this dataflow component when you have to fetch data from REST API webservice like a table. This component allows you to extract JSON data from webservice and de-normalize nested structure so you can save to Relational database such as SQL Server or any other target (Oracle, FlatFile, Excel, MySQL). This component also supports reading local JSON files or direct JSON string (Wildcard pattern supported too e.g. c:\data\file*.json).• REST API Task : Use this task when you don’t want to pull

REST API data in tabular format but want to call rest API for POST data to server, DELETE data from server or things like download HTML page, extract Authentication tokens etc where you not necessarily dealing data in tabular format. This task also allows you many other options such as saving RAW response into variable or file.

Tasks/Components in SSIS for Consuming RESTful webservice

• XML Source Connector (SOAP, File, REST) : Use this dataflow component when you have to fetch data from XML or SOAP webservice and consume data like a table. This component allows you to extract data from webservice and save to SQL Server or any other target (Oracle, FlatFile, Excel, MySQL). This component also supports reading local XML files or direct XML string.

Using SSIS JSON Source to read from REST API and load into SQL Server

• If you have need to consume REST API Service and store result into SQL Server or any other RDBMS/FLAT File then you can check this SSIS JSON Source Connector . JSON Source Connector can deformalize your nested JSON (It also supports JSONPath filter expression).

HTTP GET Request using SSIS Web Service Task or JSON/XML Source

• Calling REST API from SQL Server BI Stack usually requires programming skill. But in this section we will learn how easy it is to call RESTful Web Service using SSIS Web Service Task, JSON Source or XML Source (all Drag and drop).

• In this example we will get JSON data from ODATA Web Service.• Download and Install SSIS Lightning Pack• Create new sample SSIS package in BIDS/SSDT• From SSIS Toolbox look for items starting with “ZS”. Drag and Drop

[ZS Rest API Web Service Task] to Designer Surface• Configure HTTP Request properties as below• Configure HTTP Response properties as below• Run SSIS Package

Pass values in HTTP HeadersYOU CAN PASS VALUE IN HTTP HEADERS USING DIRECT APPROACH OR DYNAMIC APPROACH. IF YOU WISH TO PASS VALUE FROM SSIS VARIABLE THEN USE VARIABLE PL ACEHOLDER E .G. {{USER: :VARSOMEVARIABLE}}. YOU CAN ALSO USE VARIABLE FORMAT SPECIF IER E .G.

• FORMAT DATE: {{USER: :VARSOMEVARIABLE,YYYY-MM-DD}

• ENCODE INTO BASE64: {{USER: :VARSOMEVARIABLE,BASE64ENC}}

• DECODE INTO PLAIN TEXT FROM BASE64: {{USER: :VARSOMEVARIABLE,BASE64DEC}}

Calling REST API in SSIS using REST API Task, Pass headers, Body, Url Parameters

Pass values in HTTP Headers

SSIS REST Api Task – HTTP GET, Test SSIS Web Service Call, Pass Custom Header

REST API response validation ( By status code, content, header)

Pass values in Query String dynamicallyMOST OF WEB SERVICES USUALLY ACCEPT PARAMETERS VIA QUERY STRING. YOU CAN ALSO USE VARIABLE PLACEHOLDERS TO MAKE YOUR URL WITH QUERYSTRING DYNAMIC (E.G. HTTPS:/ /MYSITE.COM/ORDERSERVICE/?STARTROW={{USER::VARSTART}}&ENDROW={{USER::VAREND}}

HTTP POST Request using SSIS Web Service Task or JSON/XML Source

• When you select HTTP POST Method then by default content-type is set to application/x-www-form-urlencoded. With POST Method you can send POST data (When POST Method selected Data textbox becomes editable. You can also use variable place holders in POST data to make it dynamic.• POST data is usually in key/value format (e.g.

user=abcd&pass=mypass123) but sometimes service required data in JSON format or XML format. In that case you can submit that way. Depending on service requirement you have to set additional headers to indicate content-type (Please refer your service documentation).

Uploading files using HTTP Multipart/form-data POST Request

MANY API SUPPORT UPLOADING FILES ALONG WITH YOUR POST REQUEST. REFER THIS ARTICLE TO LEARN MORE ABOUT HOW TO UPLOAD FILES USING REST API CALLS.

Passing Credentials to your Web Service

IF YOUR SERVICE REQUIRES AUTHENTICATION THEN YOU HAVE TO MAKE SURE YOU PASS REQUIRED CREDENTIALS IN CORRECT FORMAT. BELOW IS LIST OF SOME MOST COMMON AUTHENTICATION TECHNIQUES.• BASIC AUTHENTICATION (USERID AND PASSWORD APPROACH)• TOKEN BASED APPROACH (REQUIRES ATLEAST ONE

ADDITIONAL REQUEST TO GET TOKEN)• OAUTH AUTHORIZATION• WINDOWS AUTHENTICATION (NTLM)• CLIENT CERTIFICATE

Basic Authentication – Set Authorization Header (Base64 Encoding)

HTTP STANDARD SUPPORTS BASIC AUTHORIZATION MODE IN WHICH YOU CAN PASS USERID AND PASSWORD INTO BASE64 ENCODED STRING. CHECK THIS ARTICLE FOR MORE INFORM

HOW TO PASS BASE64 ENCODED AUTHORIZATION HEADER

Token based approach – such as OAuth

• This is becoming most common approach using protocols such as OAuth where user first authenticate to service using AccountKey and SecretKey. Once authenticated you receive token which can be valid for certain duration (or infinite duration). After you receive token you can call services (each service call will include this token). Parsing token from intial response can be achieved through REST API Response Filter Expression (e.g. $.token) or use JSON Parser Task

OAuth Authorization

• All REST API Tasks/Components in Zappysys SSIS PowerPack support OAuth Authorization. OAuth is getting popular and many bigger companies already adopted this standard (e.g. Facebook, Twitter, Google, Salesforce). With this method you can connect to REST API Service without storing your Userid/Password. Only first time you have to login to using your credentials to get initial Token and after that service will continue using Access Token rather than your UserID/Password. Once Token expires it can automatically renew.  Check this for more information about using OAuth

Windows Authentication (NTLM)BY DEFAULT ZAPPYSYS REST API TASKS/COMPONENTS USE YOUR DEFAULT CREDENTIALS. YOU CAN ALSO USE HTTP CONNECTION MANAGER WITH USE CREDENTIALS > USE WINDOWS AUTHENTICATION OPTION. SEE THIS ARTICLE

Pass Client CertificateSome times you may have to pass client certificate along with your web request. Server check your certificate passed along with your request and if matched it proceeds with your request. here is the real-world use case How Azure Management Api uses Client Certificate.

Saving HTTP Web Service Response to File/VariableIf you wish to save response to file then goto response tab of REST API Task and check “Save Response” option and you can select save to file option from dropdown

Save HTTP Web Response Headers / StatusCode

If you wish to save response to file then goto response tab of REST API Task and check “Save Response” option and you can select save to file option from dropdown

HTTP Web Response ValidationREST API TASK SUPPORTS VALIDATING YOUR RESPONSE FOR CERTAIN HEADER, STATUS CODE OR CONTENT VALUE. SEE VALIDATION TAB FOR MORE INFORMATION. USING THIS FEATURE YOU CAN THROW ERROR IF CERTAIN HEADER IS MISSING FROM RESPONSE OR REJECT RESPONSE IF STATUSCODE IS OTHER THAN 200.

REST API Pagination (Loop through multiple requests)ANOTHER POPULAR CONCEPT IN REST API IS PAGING. IF YOUR RESPONSE IS LARGE THEN OFTEN SERVER RETURNS YOU PARTIAL RESPONSE AND THEN YOU HAVE TO MAKE SURE TO CONSUME REAMING DATA BY REQUESTING ALL NEXT URLS UNTIL LAST PAGE IS RETURNED.

READ THIS ARTICLE TO LEARN MORE ABOUT REST API PAGING

HTTP Web Response Error HandingREST API Task supports rich error handling. You can ignore certain errors and save error flag into variable based on any of the following criteria• Continue on any error• Continue on error with specific Response Code (e.g. 404)• Continue on error with specific string in message

Read/Write and Parse Cookies for HTTP Web Request/Response

MANY TIMES YOU HAVE TO SUPPLY AUTHENTICATION TOKEN VIA COOKIES OR ANY OTHER INFORMATION NEEDS TO BE PASSED VIA COOKIES THEN REST API TASK  HAS COMPLETE SUPPORT TO READ/WRITE OR PARSE INDIVIDUAL VALUE OUT OF COOKIE STRING. LOOK AT COOKIES TAB FOR MORE INFORMATION. COOKIES MAPPING GRID CAN HELP YOU TO MAP COOKIE VALUE TO SSIS VARIABLE (WRITE COOKIE VALUE TO VARIABLE).

Changing Headers/Url or POST data DynamicallyMOST OF FIELDS ON REST API TASK, JSON SOURCE AND XML SOURCE SUPPORT PLACEHOLDERS.USE PLACEHOLDER ANYWHERE IN THE FOLLOWING FIELDS USING {{USER::YOURVARIABLE}} FORMAT. IF YOU EDIT VALUE YOU MAY SEE INSERT PLACEHOLDER OPTION.

• URL• BODY• HEADERS

YOU MAY FORMAT DATETIME USING SPECIAL SYNTAX LIKE THIS {{USER::MYDATE,YYYY-MM-DD HHMMSS.FFF}}

Extract single value from JSON/XML Web Response (e.g. Token)

• If your web response is in JSON or XML format then you can filter it using Expression (e.g. JSONPath or XPath). Go to Response Tab and select Content Type Format from dropdown. Enter expression to select nested sub document or value from your response. If you want to save individual properties from response into multiple variables then use JSON Parser Task• Most common usecase is if you getting token from your JSON

REST API service. Assume that Service sends you response in following format but you only care about access_token property from below response. In that case you can use JSON Path expression like this $.data.access_token

Conclusion

REST API IS BECOMING MORE AND MORE POPULAR EACH DAY. WITH THAT DATA INTEGRATION FROM RESTFUL SERVICES GOING TO BE CHALLENGE. SSIS REST API TASK AND SSIS JSON SOURCE CONNECTOR CAN SOLVE SOME OF THOSE ISSUES YOU MAY FACE WITH SSIS. USE JSON SOURCE CONNECTOR IF YOU HAVE NEED TO STORE DATA INTO SQL TABLE. USE REST API TASK IF YOU HAVE NEED TO MAKE AD-HOC HTTP/REST REQUEST (SUCH AS GET TOKEN, DELETE RECORD ETC). ZAPPYSYS REST API COMPONENTS GIVES YOU TOTAL CONTROL ON YOUR REST API INTEGRATION CHALLENGES WITHOUT LEARNING PROGRAMMING LANGUAGE (E.G. PYTHON, RUBY, C#, JAVA).