Read Xml Data From Oracle Table in Informatica

Introduction

In today's globe DATA is ubiquitous and critical to the business which somewhen increases the need for integration across different platforms like Deject, Web Service etc.  When it comes to Data Integration, business needs effective advice between their software systems and ETL tool to fulfill their needs.

This blog post explains what a REST Web Service is, how you can create a Power Centre workflow, employ Residual based method to access the web services via HTTP Transformation.

REST Overview

Spider web Service provides a common platform that allows two unlike applications on various platforms to communicate and exchange letters between them over HTTP protocol. Web Services can be accessed using different methods or styles. In Spider web Service world REpresentational Southtate Transfer(REST) is a stateless client-server architecture in which Web Services are exposed equally URLs.  The typical method of accessing Web resource in a RESTful system is through various HTTP methods such every bit Become, Post, PUT and DELETE.

Rest can apply Lather Spider web Services and any protocol such as HTTP, SOAP.

High-Level Balance Architecture

Figure 1: High-Level REST Compages

REST call using Informatica Power Eye

Permit us explain with the assistance of a sample web service which is used in a Retail Manufacture. In a Retail manufacture, client can purchase various items from the retailer through online and offline and we demand a centralized repository chosen a Information Warehouse where all the transactional data is fed into. The particular details are and then farther normalized into Data Marts to form a common schema which will have facts and dimensions. On a large calibration environment, this could exist a cumbersome task in designing a data model.

To overcome this nosotros accept business needs to access the data over the network and the best way will be to use the web service. So the last order details, pricing, payment details, and shipping data are translated internally and saved in a third party system which is either in an XML or JSON format.  The tertiary-party system hosted over a secured network is accessed via a URL. Since this system is based on REST API calls, we use Informatica Power Center equally a client to access the server which is a spider web service API.

In Ability Center, you lot can use an HTTP transformation to send a request and receive a response from a REST Web service.

Pre-requisites to configure Rest call using HTTP Transformation

• Valid REST based URL supporting either XML or JSON
• Valid SSL certificates to connect to Residual URL exterior of the network
• Basic Authentication Machinery(User Proper noun and Password)

Pace by Step Procedure

Creating a Source File

Create a source file which specifies the search parameters to exist passed as input to the HTTP transformation.

Figure 2: Source File

Create HTTP Transformation

Create HTTP transformation with the following input ports:

• Username
• Countersign
• OrderNumber

Figure 3: HTTP Transformation

Base URL

Set the Base URL to become the Order details based on the input parameters passed from the source file.

Base URL: https://examination-swaggerUI/orders/search

Figure 4: Base of operations URL Settings

Set the HTTP Header parameters

Header parameters in HTTP transformation comprise header data for request and response in a specified format such as XML or JSON. Following are the parameters:

• Default Value: Specify the acceptable values supported by the web service. Past default, the response will be in JSON. If you demand XML you need to specify every bit 'text/xml'
• HTTP Name: For Residue API the default value is "Take"

Effigy 5: Header Parameter Settings

HTTP Method Selection Type

Specify the blazon of HTTP method on the URL. In our example we are trying to get the lodge detail data, and then specify the type every bit "GET"

Figure six: HTTP Method Selection Type

HTTP Output

Pass the HTTP output to either a flat file or an XML parser to parse the information and load it into the target table. In this instance, nosotros write the output of the HTTP to a flat file.

This is how the final mapping volition await like.

Final Mapping

Figure 7: Final Mapping

Create a connection in Workflow manager and run the workflow

Navigate to Workflow manager ->Connections-> Applications->HTTP Transformation

Figure 8: HTTP Application blazon

Proper name : Enter the name of the HTTP Transformation which is understandable

User Proper noun : Enter the username of the REST API URL call

Password : Enter the password for the URL

Hallmark Type : Select the Authentication blazon as "Basic"

Create a workflow for the mapping and run the workflow. Finally, view the output. In our example, for the Social club Number "ORD-123"

Figure ix: Final Output

Bug faced while connecting to HTTP Transformation

While using HTTP Transformation the most common consequence is "SSL Certificate Mistake". And so when you run the workflow which is using the final URL you will get the below errors in session logs

Root Cause

This error is caused considering the connection to the web service URL or Rest API does not include the certificate data that is needed to authenticate the connection from the certificate provider'due south end. The certificate package does non contain a certificate from a Document Authority that the Spider web service Provider uses.

Resolution

Download the SSL certificates for the URL and add to the document files located under $INFA_HOME/server/bin/ca-package.crt

Refer the below KB commodity to know how to add those certificates

HOW TO: Extract certificates from a HTTPS URL and add together to ca-bundle.crt file

Conclusion

This concludes how to create a Power Center Workflow using REST based methods to access a spider web service using HTTP Transformation, invoke HTTP commands to get information from the web service resource.

mahrsumanducke37.blogspot.com

Source: https://mastechinfotrellis.com/blog/use-informatica-power-center-restful-web-service-client

Share this post