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

Use Windows Azure Biztalk Service (WABS) to transform flat file to XML

Introduction

This sample demonstrates how to use Windows Azure Service Bus to transform flat file to XML. The XML Bridge receives the flat file, transforms and sends it to Azure Service Bus Queue.
Use Message Receiver to retrieve messages from Queue.

Step by Step

Create BizTalk Service Project.

 











Create FlatFile Schema using FlatFile Schema Wizard.


 












If you create flatfile schema then you need to set structure and Delimiter properties manually.

 

Create XML Schema .

 

 

Create Map . 

Do Mapping as per required.

Double click on MessageFlowItinerary.bcs and configure the message flow.
Add XMLOneWayBridge.
Add queue
Use Connector to connect Bridge with Queue.

Double click on XMLOneWayBridge and configure Message Type to FlatFile Schema.
 

Configure Tranform stage of XMLOneWayBridge using Map

Configure Queue. 

Create XMLQueue on Azure Service Bus.
Configure Authentication properties

Configure MessageFlowInternary , Set BiztalkServiceURL
 

Right Click on Project and Deploy.
Set Acs NameSpace, IssuerName and Secret Key.

You can get this information from Azure Portal
 

If While Deployment you will get Error
“The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel”
Then Do the following Steps:
Go to  Azure Portal -> Biztalk Service DashBoard
Download SSL Certificate 

Install this certificate to you development environment in trusted root certification authority.
 

After Deployment you can check at Azure Portal, Schemas and Map deployed.
 

  

Set the following code in MessageSender Project 
 

string acsNamespace = "NameSpace";

string issuerName = "owner";

string issuerKey = "<Secret Key>";

string runtimeAddress = https://< ;NameSpace>.biztalk.windows.net/default/XmlOneWayBridge1";

string messageFilePath = @"<Sample File Path>";

string contentType = "text/plain";

Run the Message Sender Project.

You can check Messages from tracking tab on WABS Portal.

Read Message From XML Queue.
Set the ConnectionString and Run MessagingWithQueue Sample, Download ,
<add key="Microsoft.ServiceBus.ConnectionString" value="<ConnectionString Copy from portal>" />

Source Code

Download Source code from msdn code Gallery