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



13 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. This comment has been removed by the author.

    ReplyDelete
  6. 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

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

    ReplyDelete
  8. Hi
    The concept taken here will be useful for my future programs and i will surely implement them in my study.

    Microsoft Cloud Courses in Gurgaon

    ReplyDelete
  9. it's very nice blog,and it is helps fry student's
    Biztalk online Bangalore

    ReplyDelete
  10. I must appreciate you for providing such a valuable content for us. This is one amazing piece of article. Helped a lot in increasing my knowledge.microsoft azure training in bangalore

    ReplyDelete
  11. Great Article… I love to read your articles because your writing style is too good, its is very very helpful for all of us and I never get bored while reading your article because, they are becomes a more and more interesting from the starting lines until the end.


    Microsoft Windows Azure Training | Online Course | Certification in chennai | Microsoft Windows Azure Training | Online Course | Certification in bangalore | Microsoft Windows Azure Training | Online Course | Certification in hyderabad | Microsoft Windows Azure Training | Online Course | Certification in pune

    ReplyDelete