Tuesday, 4 February 2014

Using Windows Azure BizTalk Services to Insert XML Messages into an Azure SQL Server


Introduction

This Article provides directions on the way to tack a BizTalk Services resolution that receives a XML message, processes it through a XML bridge deployed below the BizTalk Services subscription on Windows Azure, and so inserts the message into an Azure SQL Server database


Business Scenario

Fabrikam and Contoso are two business partners. Fabrikam (the retailer) sends sales order messages to Contoso (the supplier). Contoso maintains all the sales order data in table called SalesOrders, which is hosted on Azure SQL Server database. Fabrikam sends XML messages to Contoso. Hence, Contoso has to implement a solution on Windows Azure, which enables the following:

       •    Contoso must pull the XML messages at which Fabrikam drops the sales order messages.
       •    Contoso must process the message received from Fabrikam and map it to the message   for inserting the sales order in its   SQL    Server database

To enable this scenario, Contoso does the following:

  • Generates the schema of the XML message that it will receive from Fabrikam.
  • Configures an XML One-Way Bridge as part of the BizTalk Service project to enable message validation and transformation. This bridge takes a XML message, validates it against the schema generated earlier, and then transforms it to the schema required to enter a message into the SQL Server database.
  • Uses BizTalk Adapter Service to connect to the Azure SQL Server database from the XML One-Way Bridge deployed under the BizTalk Services subscription.

Step by Step

Create a Biztalk Service project.


Create XML schema that will parse the xml send by fabrikam.














Create Table on Azure SQL Database where the sales order data will be inserted.
















Login to azure Sql Portal

 Execute the script in the new query.

CREATE TABLE SalesOrder(
 CompanyCode nvarchar(50),
 PartID nvarchar(50),
 Quantity nvarchar(50),
 AskPrice nvarchar(50),
 RequestShipmentDate dateTime,
 AddressLine1 nvarchar(100),
 AddressLine2 nvarchar(100),
 AddressCity nvarchar(100),
 State nvarchar(100),
 Country nvarchar(100),
 Zipcode nvarchar(100),
 Contact nvarchar(100),
 Firstname nvarchar(100),
 Lastname nvarchar(100),
 Comments nvarchar(500),
 PlacedDate DateTime)
  
 CREATE CLUSTERED INDEX SalesOrder_CC ON SalesOrder(CompanyCode)


















Create an LOB Relay and an LOB Target for the Insert operation on the SalesOrder table.
Go to server explorer and Biztalk Adapter services.


Click on Add Sql Target; specify the connection properties for Azure Sql database.



















 Define the service bus configuration where relay will be deployed.














Generate target schema and add to Biztalk service project.
















LOB schema added to project 














Create Map that transformed SalesOrder XML 


























Drag and drop an XML One-Way Bridge from toolbox to the Bridge Configuration surface
Drag and drop the relay created in previous steps from Biztalk Adapter Services.
Connect Bridge to LOB relay.      














Set the Route action so that the outgoing message to the LOB application has a SOAP action header
Open Server Explorer and navigate to the SQL Server LOB Relay we created earlier. Right click the relay, click Properties, and for the Operations property, copy the value of the first operation














In the Route Actions dialog box, click Add to open the Add Route Action dialog box. In the Add Route Action dialog box














Configure XML Bridge.
Double click the bridge and set message type to Salesorder schema
In the transform stage select the map.
















Test the solution

Use Windows Azure Biztalk Services Explorer to send test message to XML Bridge.






















Load the test file and send.














Check the data on portal



11 comments:

  1. Hi thanks for your artice. Maybe you can answer my question:
    http://stackoverflow.com/questions/22206360/is-it-necessary-to-host-the-adapter-service-of-biztalk-services-on-runtime

    ReplyDelete
  2. Hi roxor

    yes Adapter service is required during runtime .

    the role of Adapter service is to pick the message from service bus relay and send it to Azure SQL DB .

    i am checking how it works if Adapter service is in stop state .

    you can refer the article for more knowledge of BiztalK adapter Service
    http://msdn.microsoft.com/en-us/library/windowsazure/hh689786.aspx

    Regards
    Mohit Gupta

    ReplyDelete
    Replies
    1. "i am checking how it works if Adapter service is in stop state ."

      Hi, could you already check it?

      Delete
  3. Biztalk Server Online Training - Biztalk Admin Online Training
    http://www.21cssindia.com/courses/biztalk-server-online-training-213.html
    COURSE OUT LINE
    Introductions to Enterprise Application Patterns and BizTalk Server, Understanding BizTalk Framework, Setting up a BizTalk Server Environment, Messaging Architecture, Setting up a BizTalk Server Environment Engine, Business Activity Monitoring, WCF Services Schemas in BizTalk, Transformations in BizTalk, The BizTalk Messaging Engine and Pipelines, Adapters in BizTalk, Orchestrations, Advanced Orchestrations, Integration Patterns in BizTalkBizTalk Rules with BizTalk, Testing BizTalk Artifacts, BizTalk Server Instrumentation, Error Handling, and Deployment,Tracking and Deploying BizTalk Solutions, Monitoring and Maintenance, Administration, BizTalk Server Performance andTuning, BizTalk Tools, BizTalk 2013 Features, ESB Toolkit 2.
    If you are seeking training and support you can reach me on 91-9000444287. contact@21cssindia.com

    ReplyDelete
  4. Biztalk Admin Online Training - http://www.21cssindia.com/courses/biztalk-admin-online-training-220.html
    Overview - What is Business Process? - Need to Integrate - What is Integration? - Challenges without Integration - Challenges in Business Integration - BizTalk Server - Other Products available in Market - Comparison of BizTalk with Tibco, Web methods & SAP PI - Comparison of BizTalk with SSIS and WPF - Employees to learn at their own pace and maintain control of learning “where, when and how” with boundless access 24/7by 21st Century Software Solutions. contact@21cssindia.com ---- Call Us +919000444287

    ReplyDelete
  5. Biztalk server online training - 21st Century Software Solutions
    www.21cssindia.com/courses/biztalk-server-online-training-213.html
    COURSE OUT LINE - Introductions to Enterprise Application Patterns and BizTalk Server, Understanding BizTalk Framework, Setting up a BizTalk Server Environment, Messaging Architecture, Setting up a BizTalk Server Environment Engine, Business Activity Monitoring, WCF Services Schemas in BizTalk, Transformations in BizTalk, The BizTalk Messaging Engine and Pipelines, Adapters in BizTalk, Orchestrations, Advanced Orchestrations, Integration Patterns in BizTalkBizTalk Rules with BizTalk, Testing BizTalk Artifacts, BizTalk Server Instrumentation, Error Handling, and Deployment,Tracking and Deploying BizTalk Solutions, Monitoring and Maintenance, Administration, BizTalk Server Performance andTuning, BizTalk Tools, BizTalk 2013 Features, ESB Toolkit 2.- Biztalk admin online training - 21st Century Software Solutions
    www.21cssindia.com/courses/biztalk-admin-online-training-220.html
    biztalk admin online training, biztalk admin training, biztalk server online training, biztalk server training, biztalk admin course contents, biztalk admin enquiry, ...
    Call Us +919000444287 or contact@21cssindia.com

    ReplyDelete
  6. Biztalk Admin Online Training, ONLINE TRAINING – IT SUPPORT – CORPORATE TRAINING http://www.21cssindia.com/courses/biztalk-admin-online-training-220.html The 21st Century Software Solutions of India offers one of the Largest conglomerations of Software Training, IT Support, Corporate Training institute in India - +919000444287 - +917386622889 - Visakhapatnam,Hyderabad Biztalk Admin Online Training, Biztalk Admin Training, Biztalk Admin, Biztalk Admin Online Training| Biztalk Admin Training| Biztalk Admin| "Courses at 21st Century Software Solutions
    Talend Online Training -Hyperion Online Training - IBM Unica Online Training - Siteminder Online Training - SharePoint Online Training - Informatica Online Training - SalesForce Online Training - Many more… | Call Us +917386622889 - +919000444287 - contact@21cssindia.com
    Visit: http://www.21cssindia.com/courses.html"

    ReplyDelete
  7. This comment has been removed by the author.

    ReplyDelete
  8. Hi mohit,
    your demo is worked fine and I have also gone through the other post of WCF request reply bridge sample and that also worked fine. but I struck with other scenario where I need to do select operation in SQL LOB Target and store the response in a FTP destination. can u please help I have been searching for a solution but I couldn't find it. I am new to biztalk and biztalk services.

    Thank you
    Gowtham

    ReplyDelete
  9. awful piece of information, I had come to know about your blog from my friend vimal, mumbai,i have read atleast 3 posts of yours by now, and let me tell you, your blog gives the best and the most interesting information. This is just the kind of information that i had been looking for, i'm already your rss reader now and i would regularly watch out for the new posts, once again hats off to you! Thanks a million once again, Regardswindows azure training in hyderabad

    ReplyDelete

  10. Its very great article post, share more updates.Thanks for sharing.
    Azure Online Training

    ReplyDelete