Mapping Inbound XML Data in to Single Element / Node in BizTalk Mapper

Tuesday, June 19, 2012

We need to call an SQL Server stored procedure based on parameters specified in an incoming XML message. In fact, the XML document contained a set of unbounded records that each needed to be associated with a call to a stored procedure.I could have used the debatching capabilities of the XML Disassembler pipeline component and implemented an orchestration implementing a scatter-gather like pattern, but since this solution was part of an ESB Toolkit 2.0 itinerary, I wanted to keep it as simple as possible.
Microsoft SQL Server offers native capabilities to process XML data. For instance, it is possible to supply an appropriately formatted XML document directly to a stored procedure for processing, thanks the OPENXML and other various T-SQL constructs.
That’s why I opted to bypass any pre-processing on the supplied XML document and hand it over directly to the stored procedure. This stored procedure would, in turn, iterate over the different records and process them accordingly.
In order to do that, the contents of the XML document must be specified in a single argument to the stored procedure, like so.
Serializing the entire contents of an XML document with the BizTalk Mapper
In my solution, the stored procedure is invoked through the WCF-SQL adapter. Therefore, a special document that conforms to an adapter-specific generated schema must be sent to the corresponding solicit-response send port.
In order to map the entire contents of an XML subtree structure from the source schema to a single element in the target schema, one can use the Call Template functoid in a BizTalk map.
How does it Work?
In the Call Template functoid, notice the use of two different templates.
The first one, is the entry-point for the Call Template functoid when running the map. It declares a single parameter that corresponds to the node on the source document under which the entire structure must be serialized to the target node.
<!--Uncomment the following xslt for a sample Xslt Call Template
that creates a Field element whose value is the concatenatation of the
two inputs. Change the number of parameters of this template to be 
equal to the number of inputs connected to this functoid.-->

<xsl:template name="called-template">
  <xsl:param name="param1" />
  <xsl:element name="xmlDocument" namespace="">
    <xsl:text disable-output-escaping="yes">&lt;![CDATA[</xsl:text>
    <xsl:call-template name="identity" />
    <xsl:text disable-output-escaping="yes">]]&gt;</xsl:text>
First, the template produces an XML element, based upon its name and namespace as required in the target schema. In this case, the element corresponds to the name of the argument to the stored procedure as generated by the WCF-SQL adapter.
Notice that the serialized data is wrapped around a <!CDATA[]]> tag, so as to eliminate the need to escape XML-reserved characters.
The serialized data itself is produced by calling the second template. This one is none other than the identity template.
<xsl:template name="identity" match="@*|node()">
    <xsl:apply-templates select="@*|node()" />
This template iterates over all elements, texts, comments and processing instructions and recursively copies the contents to produce its output.

Read more ...

Advanced Users Biztalk WIKI : Useful How to Links Part - 3

Thursday, June 14, 2012
Useful How to Links Part - 3

BizTalk Administrator's Checklist Compiled by Microsoft BizTalk Support

Muenchian Grouping and Sorting in BizTalk Maps

Xslt Template StyleSheet For Biztalk Mapping : Query in Biztalk Map

AppFabric-enabled WCF Data Service Walkthrough (C#)

Querying DataSets – Introduction to LINQ to DataSet

Accessing BRE RuleEngine Using .Net Framework
Few links that may help:                                                                

(MSDN: Walkthrough: Executing the Policy Programmatically)

Read more ...

Advanced Users Biztalk WIKI : Useful How to Links Part - 5

Tuesday, June 12, 2012
 Useful How to Links Part - 5

Bre Static Support Key

Calling Data Access from Ado.Net

Sso Configuration Storing Values

Xml Document XPath Sample

ESB Dynamic Routing using Itineraries

Read more ...

Advanced Users Biztalk WIKI : Useful How to Links Part - 4

Friday, June 8, 2012
Useful How to Links Part - 4

Iterate ArrayList in BRE Business Rules

Programming with Business Rules Framework

How to Script Import/Export and Deployment of Business Policies using FileRuleStore

SSO Configuration Store Application

Tool Source Code:

Here are a few TechNet Wiki articles that have become available recently. I have usually found it difficult to find relevant content on the TechNet Wiki so I thought it would be good to highlight a few of these.
Improving performance when executing a BRE rule:

Implement ordered messaging with BizTalk and SSIS:

BizTalk load testing in VS 2010:

Load testing for simultaneous BizTalk unit tests:

Read more ...

Advanced Users Biztalk WIKI : Useful How to Links Part -2

Wednesday, June 6, 2012
Useful How to Links Part -2

Implementing Biztalk Pipeline Trace

Biztalk Orchestration Tracing Using Biztalk Diagnostics Library

High Performance Message Transform Alternative to XslTransform vs Xsl 

Dynamic Mocking With N-Mock

Biztalk 2010 Documentation

Validating Incoming Xml using BRE

BizTalk: Delivery Notification in Direct Send Ports

BizTalk XSLT Reuse (xsl:include) by using MS Build

BizTalk Server: Performance Tuning & Optimization
Read more ...

Advanced Users Biztalk WIKI : Useful How to Links Part -1

Monday, June 4, 2012
 Useful How to Links Part -1

Restful .Net : MSDN Book Link

Rest Service Tutorial - Code Project

Microsoft Biztalk server 2010  Pre Configured  on Windows Server with Sql Server 2008 R2

Biztalk Server Technet Wiki Documentation by Microsoft Developer Community

FTP Server File Rename Command script
 RNFR %FileReceivedFileName%, RNTO Newfilename

Generating Proxies from WSDL Web Service using wsdl.exe in .Net 2.0
Wsdl /language:language  /protocol:protocol /namespace:myNameSpace /out:filename /username:username /password:password /domain:domain <url or path>
Equivalent Action : Visual Studio --> Add Web Reference

Generating WCF Proxy from Asmx / Wsdl Web Service
svcutil.exe [/t:code]  <metadataDocumentPath>* | <url>* | <epr>
Equivalent Action :Visual Studio --> Add Service Reference

Funtoid Detailed Reference
Read more ...

BizTalk Performance Analysis Using PAL Tool, Walk Through Using PAL Tool

Friday, June 1, 2012
Post By Clint Huffman

BizTalk Performance Counters

Microsoft BizTalk Server 2006 shipped with about 294 performance counters. This means a BizTalk Server implementation with at least 2 servers for redundancy means there are at least 588 BizTalk performance counters that may need to be analyzed. Therefore, a performance monitor log analysis tool is helpful with analyzing BizTalk performance counters.

Consider the Operating System Performance

Many BizTalk performance issues can be narrowed down by analyzing the resources of the operating system (CPU, disk, memory, and network). For example, if the tracking (DTADB) database file is heavily using a disk, then reducing the amount of tracking would be a logical step towards alleviating the bottleneck.


The PAL (Performance Analysis of Logs) tool is a powerful tool that reads in a performance monitor counter log and analyzes it using known thresholds.  A template is created from the PAL tool and imported to the server’s Performance tool as a new counter.  This includes relative BizTalk and SQL metrics that will be tracked.  The resulting blg files are analyzed by the PAL tool and thresholds are applied once questions are answered regarding number of CPU, memory, etc.  A report is produced by the PAL tool in an easy to read format with graphs.  Tallan will use this information to make recommendations for performance improvements. 


Threshold files take the questions answered in the tool and apply them to the results.  This is a quick and easy way to see where the weaknesses are without lengthy analysis. 
An easy to use GUI interface which makes creating batch files for the PAL.ps1 script.  This GUI also creates a template Perfmon template and allows the user to answer questions specific to the environment such as CPU count, RAM, etc. 
A GUI editor for creating or editing your own threshold files.  This allows the user to modify, add, and remove threshold values and files for a more custom implementation. 
Creates a HTML based report for ease of copy/pasting into other applications. 
Analyzes performance counter logs for thresholds using thresholds that change their criteria based on the computer’s role or hardware specs. 

Required Downloads 

Microsoft .NET Framework 3.5 Service Pack 1 (Partial package – internet access required)
Microsoft .NET Framework 3.5 Service Pack 1 (full package – no internet access required)
PowerShell v2.0 (Windows Management Framework (Windows PowerShell 2.0, WinRM 2.0, and BITS 4.0)) 


Download PAL from : developed by Clint Huffman
Unzip contents and run the installation msi.  Note, only supported on x32 machines – this requirement does not restrict using product on x64 machines because the log files can be processed locally. 


Open PAL from installation directory 


Click on ‘Threshold File’ tab, select ‘Microsoft BizTalk Server 2006/2009/2010’ in the Threshold File Title drop down.  Click ‘Export to Perfmon Template File’.  Note, optionally one of the features is to edit the template file – click ‘Edit’ and add/remove metrics.

Select the file location: \\servername\e$\PAL\perfmonTemplate or the central directory on the server environment you will be testing on.

Create Perfmon Log from Exported Template 

Remote Desktop into the server you want to performance the analysis on (or continue on local machine) and open Perfmon tool – expand Performance Logs and Alerts.

Right click on ‘Counter Logs’, ‘New Log Settings From…’

Select the template that was previously created.

Click Ok

A new dialogue box will open, Click ‘Log Files’ tab and click ‘Configure’ to set the location of the Perfmon log (blg) files.

Set the location of the log files.  IMPORTANT – use a network drive so that there is no drive IO interference.  Here we used E:\PAL\PerfmonLogFiles directory.  Click OK.

Click on ‘Schedule’ tab and set when the Perfmon should run.  Once the log file is created, we will go back to our local environment and load it with PAL tool.

After the log completes, go to the directory that was set up above and verify blg file exists.

Run PAL Against Log File
Go back to your PAL tool on local machine and click on ‘Counter Log’ tab.  Click on path button and select the log file that was created in the above step by the server.

Click Open

Click tab ‘Questions’.  This will allow you to answer preset questions that will provide the threshold variables by the tool.  Answer the questions by entering values for each.

Next, Click ‘Output Options’ and set the time slice Analysis interval.  Use AUTO to automatically separate into 30 time slices.  This will show as a data point for each metric (30 in this case).

Next, Click ‘File Output’ tab and select the Output Directory to store the HTM report. 

Click tab ‘Execute’, make sure ‘Execute: Execute what is currently in the queue’ and click ‘Finish’ to start the powershell scripts.  After scripts are completed, browse to the report directory set up previously if report does not automatically open.

Open Report

View Report and Analyze
A Report with thresholds and alerts will open. 

Usage For Windows Server 2008 PerfMon

The PAL tool is can be used to create a Microsoft Performance Monitor (perfmon) template file from PAL threshold files and can analyze the perfmon log after its collection period. In this example, we will create, gather, and analyze a BizTalk performance monitor log file.

Collect a Microsoft Performance Monitor (perfmon) log

1. Export a Microsoft Performance Monitor Log from the BizTalk Server threshold file.
a. Selecting the Microsoft BizTalk Server Server 2006 threshold file and clicking the Export… button, then save the file. This is a perfmon template file. 


2. Copy the perfmon template file to the BizTalk server(s) you have chosen for analysis. This includes the Microsoft SQL Servers hosting the BizTalk databases.

3. Create a new performance monitor log using the template exported in step 



Then Right Click on User Defined template And

Let PerfMon to Run Adequate Time And then collect the PerfMon report

1. For more information on how to create performance monitor logs from a template file, please refer to the Windows help documentation.
a. Adjust the perfmon log settings if needed.

4. Start the new performance monitor log. Stop the perfmon log when the collection period is over.

5. Copy the perfmon log to the installation directory of the PAL tool.

Filling out the PAL Wizard Form
  1. Counter Log Path: Specify the path to the Microsoft Performance Monitor (perfmon) log. The log can be in any of the known perfmon log formats such as BLG (binary) or CSV (text). Use the ellipsis button to browse for the perfmon log. If the perfmon log file is in the installation directory of PAL, then use the drop down arrow to select one of the perfmon log files.
    Note: Multiple log files can be merged by separating them with semicolons, but this may produce unpredictable results. PAL is best used with logs containing data from a single computer.
  2. Date/Time Range: This is the date/time range that you can restrict the analysis to. For example, if you did load testing on BizTalk during a specific time.
  3. Threshold File Title: This is the threshold file that you want to use to determine thresholds that will be analyzed by PAL. If you do not see a product specific threshold file, then consider using the default System Overview threshold file. The System Overview threshold file analyzes the basic operating system performance counters only.
  4. Question Variable Names: The question variables will be different depending on which threshold file is selected. Click on each question variable name and answer the question in respect to the perfmon log chosen earlier.
  5. Analysis Interval: Specify the time (in seconds) that you want the PAL tool to analyze the perfmon log. Choose “AUTO” to have PAL automatically detect and choose an appropriate interval size. Choose “ALL” to have PAL analyze all of the data in the perfmon log. Be careful about choosing “ALL” because it is extremely resource intensive. The Analysis Interval determines how the perfmon log will be broken up when analyzed. For example, if you gathered a 24 hour log and choose a 1 hour analysis interval, then PAL will analyze each hour in the log for minimum, maximum, average, and thread values for that time interval. The data points in the charts generated by PAL are based on the analysis interval. “AUTO” is the default and is recommended.
  6. Output Options: You can optionally specify an output directory for the output, the file name format for the HTML report, and an optional XML document output. The XML output is useful if you want to analyze the results using another tool.
  7. The Queue: The queue is really just a batch file with line feeds for readability.
  8. Execute:
    • Execute: Execute what is currently in the queue.
    • Add to Queue: Do not execute yet, but add the current configuration into the queue and restart the wizard allowing you to add more. This is useful if you intend to "batch" the processing while you go to lunch or overnight.
    • Execute and Restart: This executes what is in the queue now and restarts the wizard. This is useful if you want to start processing one of the logs right away while answering the questions for the other logs.
  9. Execute as a low priority process: This will run the PAL.vbs script at a low process priority so that when PAL.vbs is running, it will have the little to no impact on your computer. PAL processing can be very resource intensive. This option will make the processing take longer.
Interpreting the Report

The report generated by the PAL tool is simply an interpretation of the perfmon log data using generalized thresholds. The intention is to assist with performance analysis, but not to replace traditional performance analysis. Therefore, you should have a working knowledge of the BizTalk architecture and BizTalk performance analysis.
The thresholds used in the BizTalk threshold file include operating system thresholds (CPU, disk, memory, and network), Microsoft SQL Server counter thresholds, and BizTalk counter thresholds. The BizTalk thresholds generally focus on host throttling, adapter latency, service instance statistics (suspended, dehydrated, etc.), database sizes, and memory usage.
The PAL report is separated by categories and in each category is a collection of analyses. Each analysis focuses on a specific performance counter. If any of the thresholds are exceeded, then an alert is raised. The number of alerts in each analysis and the alert condition (typically Warning or Critical) typically indicate the severity of the results. For example, a critical alert in Pool Paged Bytes is a very serious condition and should be resolved immediately. When critical alerts occur, go to the analysis section associated to the alert. In the analysis section, there is typically a description of the analysis, a description of the thresholds used, and a link for more information on the topic.
The analyses in the report contain content describing the purpose of the analysis, why the thresholds are there, and references to more information. To learn more about interpreting the PAL report for BizTalk analysis, then read the following article:

Using the Performance Analysis of Logs (PAL) Tool
Read more ...