13th of November, 2013 / / No Comments

Introduction

This blog demonstrates the key fundamentals in creating, invoking and deploying a Windows Azure BizTalk Services (WABS) Message Flow Itinerary to Azure, inserting data into a Windows Azure SQL Database.

It also uses the new Scripting feature of Azure Mobile Services to integrate with BizTalk Services.

Scenario

In the Managed Services Business Unit for the company I work for, a person every month was using MS Excel to generate monthly reports based on various Service Desk metrics. In this case, the third party Service Desk provider exposes a set of reports (Members, Agreements, Service Requests and Companies) as XML over a HTTP Get request. The data was then collected and then manipulated to create the reports in MS Excel. This was a manual process and took the best part of a day to complete. This was Inefficient and needed to be automated. I saw this as an opportunity to use the new WABS and place the entire workflow into Azure.

For this solution, the XML from the Service Desk application reports are sent to a BizTalk Service Bridge where, according to the type of message the data mapped and saved in a set of Azure SQL Database Tables via Stored Procedures (which is then later reported on using Azure SQL Reporting Services). This workflow is kicked off by a Mobile Service Schedule script set to run once a day.

Architecture

The following diagram shows the architecture of the solution.

1. The Client application, in this case a Mobile Service Script executes and sends a request to the Access Control Service in Azure to acquire a security token necessary to be authenticated by the underlying BizTalk Service. The Script uses the OAuth WRAP Protocol to acquire a security token from ACS. In particular, the server script sends a request to ACS using a HTTPS form POST. The request contains the following information:

  • wrap_name: the name of a service identity within the Access Control namespace of the BizTalk Service (e.g. owner)
  • wrap_password: the password of the service identity specified by the wrap_name parameter.
  • wrap_scope: this parameter contains the relying party application realm. In our case, it contains the http base address of the BizTalk Service (e.g. https://MyBiztalkService.biztalk.windows.net/default/3rdPartyOnRamp)

2. ACS issues and returns a security token where it is stored for the duration of the script.

3/4. Then, each Script requests XML from the Service Desk 3rd party URL in Batches (this is due to a limitation in the size of messages being send to the BizTalk Service Bridge)

5. When each Message is received from the 3rdParty, the message report-type is set (an attribute of the XML) and then sent to the BizTalk Service Bridge by each Script.

6. BizTalk Service Bridge does the following

  • Determines Report Type (Service, Company, Member, Agreement)
  • Maps to correct Stored Procedure (Service, Company, Member, Agreement) message.
  • Sends message to Service Bus Relay.

7/8 The Service Bus Relay is kept active via a VM running BizTalk Services WCF Services runtime where it is “relayed” to an Azure SQL    Database.

Prerequisites

Visual Studio 2012 with Windows Azure BizTalk Service SDK.

Building the Sample

Proceed as follows to set up the solution.

Create the BizTalk Service

This assumes you have already created a new BizTalk Service within Azure – refer to this article for a guide if you have not done so http://www.windowsazure.com/en-us/manage/services/biztalk-services/provisioning-biztalk-service/

In order to manage Your BizTalk Service you need to click on the Manage Icon and you will be redirected to a separate BizTalk Services Portal (yes another management portal!!).

The most useful part of the Portal for developers is Tracking where you can do some basic debugging of your BizTalk Services once deployed.

If this is your first BizTalk Services project there should be nothing deployed here.

Create and Configure the Mobile Service Scripts

Create a new Mobile Service

Click on the newly created Mobile Service and create 4 scripts – In our case one for each 3rd Party XML reports.

  1. Agreements
  2. Company
  3. Member
  4. Service

Configure each script – In this case I want each script to execute daily at 11pm.

On the Script tab copy and paste the following code into each script. The content of the script is not important. In fact it won’t work because I don’t make public the URL of the HTTP XML get request in order to get the xml required. What is important is what it’s doing:

  1. Gets an ACS Token for authenticating against the BizTalk Service Bridge Endpoint;
  2. Retrieve the XML from the 3rd Party; and
  3. Send the XML to the BizTalk Service Bridge (setting the Authorization token to the value in 1.) exposed as a RESTful service endpoint in this case an https POST.

var data;
var request = require("request");
var skip = 0;
var token;
//Delay between each call the the 3rd Party URL
function delay(time) {
var d1 = new Date();
var d2 = new Date();
while (d2.valueOf() < d1.valueOf() + time) {
d2 = new Date();
}
}
//Entry Point of the script must be the same name as the Script.
function 3rdParty_Agreements() {
//get ACS Token
request.post({
headers: {'content-type' : 'application/x-www-form-urlencoded'},
url: 'https://jodypetronibizsvc-dev.accesscontrol.windows.net/WRAPv0.9/',
body: 'wrap_name=owner&wrap_password=xxxxxxxxxxxxxx&wrap_scope=http%3a%2f%2fxxxxxxxxxxxxxx.biztalk.windows.net%2f3rdPartyOnRamp'
}, aCScallback);
}
//Call back of ACS Call
function aCScallback(error, response, body){
var str_array = body.split('&');
for(var i = 0; i < str_array.length; i++)
{
if(str_array[i].substring(0,18) == 'wrap_access_token=')
{
token = str_array[i].substring(18);
}
}
//when done connect to 3rd Party
call3rdPartyLoop(skip);
}
function 3rdPartyCallback(error, response, body) {
data = body;
console.log(data);
if(data != ""){
console.log(skip);
callBridge(token,data,function(){
skip = skip + 10;
call3rdPartyLoop(skip); }, delay);
}
}
//&p = Type of Query (reports)
//&r = Report Name (Agreement, Service, Company or Member)
//&q = Filter (if any)
//&l = Number of records to be returned
//&s = Skip – number of records to be skipped next time the query is run. This is useful in a Batch scenario.
function call3rdPartyLoop(skip)
{ request("http://aus.my3rdParty.net/v4_6_release/webreport/?c=kloud&u=reports&p=reports&r=AgreementList&q=AGR_Detail_Type_Desc='Incidents'&l=10&s=" + skip, 3rdPartyCallback);
}
//Call BizTalk Service Bridge
function callBridge(token,data, call3rdParty, delayf){
var newString = data.replace("",'');
var newString1 = newString.replace("","");
token = 'WRAP access_token=\"' + token + '\"';
var http = require('https');
var post_options = {
host: 'xxxxxxxx.biztalk.windows.net',
path: '/default/3rdPartyOnRamp',
method: 'POST',
headers: {
'Content-Type': 'application/xml',
'Authorization': decodeURIComponent(token) }
};
var callback = function(response) {
var str = ''
response.on('data', function (chunk) {
str += chunk;
});
response.on('end', function () {
console.log(str);
delayf(5000);
call3rdParty();
});
}
var post_req = http.request(post_options, callback);
//This is the data we are posting, it needs to be a string or a buffer
post_req.on('error', function(e) {
console.log('problem with request: ' + e.message);
});
post_req.write(newString1);
post_req.end();
}

Create the Service Bus Namespace

Go ahead and create a new Service Bus using the Azure Management Portal. This will be used later for our BizTalk Service Relays.


Create the Azure SQL Database – KloudReportingDB

This will be used to store our data from our 3rd Party Service Desk Providers. Create a new SQL User also as this will be used later to configure the BizTalk Services LOB Relay endpoint.

4 tables Types – One each for each type of report we are saving to each table (Member, Service, Company and Agreement). These have the same column definitions as the Tables. This will enable us to send a batch of records to a SQL Stored Procedure. You will need to connect to the SQL Azure Database via SQL Management Studio in your on Premise Development environment in order to create table types as you can’t do this via the Azure Portal.

  1. InsertAgreementRequest
  2. InsertServiceRequest
  3. InsertMemberRequest
  4. InsertCompanyRequest

4 Tables– one each for each type of report we are saving (Member, Service, Company or Agreement).

  1. Agreement
  2. Service
  3. Member
  4. Company

4 Stored Procedures – One for each type of report we are saving. Each Stored Procedure takes a Table Type according to the type of report being saved (Member, Service, Company and Agreement). Again you will need to do this with SQL Management Studio in your on Premise Development environment as you won’t be able to compile your Stored Procedures as the Azure Portal isn’t aware of the Table Types you have created.

  1. sp_InsertAgreements
  2. sp_InsertCompanies
  3. sp_InsertServices
  4. sp_InsertMembers

This is an example of one of the SP’s


CREATE PROCEDURE [dbo].[sp_InsertCompanies] @insertComanyRequest InsertCompanyRequest READONLY
AS
BEGIN

UPDATE dbo.connectwise_company
SET [company_recid] = r.CompanyRecId,
[company_name] = r.companyname
FROM dbo.company c
JOIN @insertComanyRequest r on r.CompanyRecId = c.Company_RecId


INSERT INTO dbo.company (Company_RecId, company_name)
SELECT r.CompanyRecId, r.companyname
FROM @insertComanyRequest r
WHERE r.CompanyRecId not in(SELECT Company_RecId FROM dbo.connectwise_company)


END
GO

Create the Service Bus Relay.

Install the BizTalkAdapterService.msi (refer to post on how to do this http://rickgaribay.net/archive/2013/10/20/wabs-biztalk-adapter-service-installation-in-seven-steps.aspx). After the installation is complete, you’ll notice the BAService has been created in IIS.

Through Visual studio you connect to this Site and create a connection to a LOB application, in this case a Windows Azure SQL Database. Go ahead and do this now.

After you have added the BizTalk Adapter Service create a new SQL Target.

Right click on SQL and select Add SQL Target… Click Next

From the Azure SQL Database you created earlier fill in the required details then click Next.

Select the 4 Stored Procedures we created earlier in the SQL Azure Database and then click Next.

Enter the required security Information to connect to the SQL Azure Database then click Next.

Go to the Service Bus we created earlier and click Connection Information on the bottom of the page. You will be presented with a screen similar to the following you will need this to complete the next screen in the wizard.

Enter the required information (Namespace, Issuer Name and Issuer Secret) from the Connection Information screen. The Relay Path is the name of the Relay and the Target sub-path identifies the various sub targets under the Relay. You can call this anything you want. We will use the Target runtime URL Later in Visual Studio when configuring the LOB Endpoint.

At this point it’s worth noting what we are about to do. If you now in the Azure Portal click the Relays tab for our Service Bus you will see there are no Relays. Now click Create in the wizard.

When the SQL LOB wizard is complete you will now see that the Relay has been created with a Listener count of 1.

Now jump back into IIS and you should see a new Virtual Directory, this keeps a connection open with the Service Bus Relay. When a client connects to this Relay it is routed to the final endpoint in our case a SQL Database which just happens to be in Azure.

Visual Studio Solution

At this point we are ready to create our BizTalk Services Project. Go ahead and create the project. Visual Studio should now contain a BizTalk Services project with a blank Message Flow Itinerary.

This is what your Itinerary should look like at the end. More details on configuring this Itinerary is specified in the sections to follow.

  1. 3rdPartyOnRamp – an XML One Way Bridge, this is the exposed RESTful Endpoint to which clients will connect.
  2. Various Route Filters are configured. There is on for each route in the Itinerary.
  3. Various XML One Way Bridge are configured with message type and map. There is one for each type of message.
  4. Various Route Actions are configured. There is one for each SQL operation.
  5. LOB Service Bus Relay Endpoint is configured.

XML Schemas in the project

  1. webReport_Service.xsd – represents the Service XML returned by the 3rd Party Report URL.
  2. webReport_Company.xsd – represents the Company XML returned by the 3rd Party Report URL.
  3. webReport_Member.xsd – represents the Member XML returned by the 3rd Party Report URL.
  4. webReport_Agreement.xsd – represents the Agreement XML returned by the 3rd Party Report URL.

These were generated by running each report and creating an XSD based on each instance.

Important: In order to add the schemas from your Azure SQL Database go back to the Server Explorer and the SQL LOB Node, select the one you created, right click and select Add schemas to ProjectName… This will create a Directory to your Project called LOB Schemes and add your schemas to it where we use them later to map from the source to the SQL Stored Procedure.

Maps in the project

  1. Company_to_CompanyTableDB.trfm – Maps from the Source 3rdParty Schema webReport_Company.xsd to the Generated Schemas for the Stored Procedure sp_InsertCompanies.
  2. Service_to_ServiceTableDB.trfm – Maps from the Source 3rdParty Schema webReport_Service.xsd to the Generated Schemas for the Stored Procedure sp_InsertServices.
  3. Agreement_to_AgreementTableDB.trfm – Maps from the Source 3rdParty Schema webReport_Agreement.xsd to the Generated Schemas for the Stored Procedure sp_InsertAgreements.
  4. Member_to_MemberTableDB.trfm – Maps from the Source 3rdParty Schema webReport_Member.xsd to the Generated Schemas for the Stored Procedure sp_InsertMembers.

Configure 3rdPartyOnRamp Xml One Way Bridge.

In the Request Message Types add the 4 source schemas as we will be sending all 4 types of messages to this Bridge.

Then configure the Enrich Stage, Property definitions and for each message type create a property called ReportType based on an Xpath expression. We will use this later to determine the route and therefore apply the correct map to transform the source XML to the SQL Stored Procedure XML.

When you finish you should have 4 property definitions called ReportType for each Source Schema.

Configure the Routing Filter

Based on the ReportType value in the incoming message – set this filter value. This will determine the path of the message in the Itinerary flow. When finished you should have configured 4 Route Filters with the following, each connecting to the correct XML Bridge (with the right filter of course):

ReportType=’Agreement’;

ReportType=’Service’

ReportType=’Member’

ReportType=’Company’

Configure XML One Way Bridges

Set the Message type to the type of message and the Map to use. Do this for each XML One Way Bridge.

Configure Route Action

This is where we set the Action of the Message to be sent to the Endpoint. You can get this from the Configured SQL LOB endpoint we have already created by selecting the correct Operation value, one of:

TypedProcedure/dbo/sp_InsertAgreements

TypedProcedure/dbo/sp_InsertCompanies

TypedProcedure/dbo/sp_InsertMembers

TypedProcedure/dbo/sp_InsertServices

At run time the BizTalk Services infrastructure creates a WCF Service and this Soap Action is set. At a very high level the Soap Action sets the operation to be performed, in our case the Stored Procedure to be called. (This is similar to configuring a standard BizTalk Send Port for SQL LOB WCF Adapters)

Configure LOB Endpoint

Finally we need to configure the Service Bus Relay URL. Here we enter the details of the Service Bus Relay we created earlier with the BizTalk Adapter Service wizard. You may need to View the Connection Information of the Service Bus again in Azure. The address is the Target Runtime URL we noted earlier when setting up the SQL Target in Visual Studio


Deploying the Project to BizTalk Services in Azure.

When you created your BizTalk Service in Azure you would have had to create an ACS Namespace you will need this namespace, owner and the Secret Key in order to deploy. You can get this information via the Access Control Namespace tab in the Windows Azure Portal in the Active Directory section of Azure.

Once deployed you may view the deployed artefacts via the BizTalk Services Portal described earlier in this post.

Test the Solution

Testing the solution is easy. Simply run one of the Mobile Services Scripts and the data from the 3rd Party appear in the Azure SQL Database Tables (as if by magic!!)

Conclusions

After reading this post you are able to send XML messages to a BizTalk Service Bridge Endpoint(if authenticated) using Mobile Services Scheduled scripting and have that BizTalk Service route a message based on a value in the XML Message to a Stored Procedure in a LOB SQL Server Database instance.

One last point – Don’t do what I did…When I turned off my Development environment (aka my laptop) the Relay disappears and therefore the final step in the Message Flow Itinerary doesn’t work, it generates an “End point doesn’t exist” exception because it’s not there. That’s because the BizTalk Adapter Services Web Site running on my laptop creating the Relay isn’t running. My challenge was that everything in this design (apart from Visual Studio) is located in Azure. The solution here was to create a new VM and install only the BizTalk Services runtime. Then I ran into another problem when I tried to connect to the Adapter Service BAService on the VM I couldn’t from my laptop. So I then installed Visual Studio on the VM and I could connect to the Runtime. Problem is now I have to duplicate my LOB Endpoints on my Laptop as well as in my Azure VM so it’s still connecting to the Relay when I go home at night. If I ever want to test my solution while doing a Dev/Deploy cycle for instance I have to remember my laptop and the VM were both running so I just had to remember to stop the VM IIS as this also caused errors.

Category:
Azure Platform, BizTalk
Tags:
, , , , , , ,