Tenalinaga's Blog

I am frog in a well. Every time I jump out of the well; I relialize I am in bigger well and eventually get bored which motivates me to get to next level.

Multi-record Insertion into Oracle DB using ESB Messaging Scenario

Multi-Record Insertation into any table within Oracle DB using ESB Messaging Scenario based on Message Type

Idea of this sample seems to pretty be intersting when I first thought about it. we are originally thinking more in lines of having seprate WCF service to inert into each table and Main Orchestration having to call sepecific WCF service dynamically. These services need individual orchestration to published as WCF service.

But here Idea is to develop one single service which can be consumed by generic orchestration which accepts XMLDocument type. Input XMLDocuments would be one among set of defiend schemas that hold data that should be inserted to corresponding tables. We achive this WCF service with out orchestration (just messaging senario) using Itineraries.

Step1: Creation of 2 test Oracle Tables

For Demonstration I created 2 tables in HR database.  I want to insert into any of the 2 tables based on the incoming schema.

Oracle Test Tables

 

 Step2: Creation of Schemas and Maps

using Consume Adapter Service -> OracleDBBinding I have generated  Request schemas for table insert operation. Generated schema structure for “TABTESTPRODCUTS” and “TABTESTSTORES” table is shown side by side.

Oracle Insert Schemas

I want service response to be in "standard normalized manner". Standard response schema to notify sucess/failure is below

Normalized Response

So I developed schema and maps for Oracle Insert response form oracleDB to normalized schema. 2 map are below.

Oracle Insert Schemas

I also created 2 schemas (Original Data Schemas) which has data that should be inserted into oracleDB. (In a genralized sense these can be defined set of defined schemas that hold data for corresponding tables in OracleDB)

Native Data Schemas

Then I created maps to map Original Data Schemas to oracle request schemas.

Maps To Request Schema


Step3: Creation of WCF Service Receive Port and Dynamic soilict-response Port

We proceed to cretate a WCF service using WCF schema publishing Wizard and create a receive location. Input Schema is of type “Microsoft.XLANGs.BaseTypes.Any” and Output response schema is standard normalized response. Configure Receive Location is shown below

WCF Service Receive Port

ItinerarySelectReceiveXML has itinerary selector component that can select the itinerary. we use Resolver.Itinerary" to fetch itinerary from Database. Itinerary we are about to develop in next steps is named "GenricOracleInsert". We also create a Dynamic Solicit Response port and provide configuation as shown below

Dynamic Solicit Respone Port

Step4: Creating BRE Polcies

We will need to create 3 BRE policies.

1) First one to Select the Map based on the incoming message type for the input schema.


 BRE Request Map

2) Second Policy To Set the OutBound Transport End Points


BRE Request Map

3) Thrid Policy to set the Map to normalize response to Standard Reponse.

 BRE Request Map

Step5: Itinerary Design

Now we have all the required artifact. we need deploy them in BT application. Then we can proceed with Itinerary Design

Itinerary is like routing slip and steps associated are shown below.

based on messagetypes BRE policy provide information to perform dynamic routing and transofrmations.

 

 Itinerary Design


Step6: Generic InBound Test Orchestration

Generic Inbound Test Orchestration

We now create a test orchestration to test the WCF service we create. This genric orchestration accepts valid XMLDocuments as input.

Typically incoming XMLDocuments are validated by the receive XMLdisassembler pipeline component and subscribed by the orchestration.

Then we route defined Orginal Data schemas to the WCF service (remember WCF service input schema is also generic which accepts any valid XMLDocuments).

Then baed on BRE policies it gets transformed, and Outbound transport context properties are set and subscribed by dynamic solicit response port.
On the response from solicit-response port we choose a map based on policy in BRE to get normalized response.

Generic Test Orchestration for the WCF service would look like.

Itinerary Design

Advertisements

3 responses to “Multi-record Insertion into Oracle DB using ESB Messaging Scenario

  1. Sandeep January 24, 2011 at 3:36 am

    Is this possible using BizTalk 2006, will it require Adapter pack 2.0? We have situation where we need to pull few rows from Oracle 9i DB using BizTalk 2006 and Enteprise Adapter Pack, i have posed the same in microsoft newsgroup as well.

    Appreciate you help.

    Thanks.

  2. tenalinaga January 24, 2011 at 12:17 pm

    Hi Sandeep,

    You will beed adapter pack with WCF Oracle adapter, and ESB installed on your machine for the sample to work.

    Thanks,
    TenaliNaga

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: