Yesterday we discovered something about the use of custom extension objects in custom XSLT.
Imagine that you need a counter value in your mapping. Since variables in XSLT can only be set once, you need to use custom code for this.
In our case we wrote a small helper class that has a counter variable.

public class Counter
{
    private int _counter = 0;

    public int GetCounter()
    {
       return ++_counter;
    }
}

I just called this in my map and tested it. It worked perfectly.

But then I fired a few concurrent calls to our BizTalk process, resulting in the map being executed multiple times in a very small time frame.
We noticed that the counter value wasn't correct anymore. After some analisys it seemed like the instance of the Counter class was shared over multiple executions of the map.
Our assumption that BizTalk creates a new instance of the class every time the map is called, was wrong.

We came up with a quite simple solution for this. Instead of just having 1 counter, we created a Dictionary that can hold multiple counter values.
The key for this Dictionary is a Guid (as string) and the value is the actual counter. Each map has it's own Guid that makes it unique.

public class Counter
    {
        private Dictionary _counter;

        public Counter()
        {
            _counter=new Dictionary();
        }

        public string NewGuid()
        {
            return Guid.NewGuid().ToString();
        }

        public int GetCounter(string guid)
        {
            return ++_counter[guid];
        }
    }

So this is how our class looks like with this Dictionary. At the top of your map you just create a variable that holds the Guid, and you use it every time you call the GetCounter method.

<xsl:variable name="Guid" select="Counter:CreateGuid()"/>

<xsl:value-of select="Counter:GetCounter($Guid)"/>

 We are still not quite sure how long BizTalk keeps this in memory. But to be sure we added a method to the class that deletes one row in the Dictionary at the end of a map.

 Tim D'haeyer, CODit

A lot of custom pipeline development involves reading message values, mostly using Xpath queries.  As we all know, it is a best practice to implement pipeline components in a streaming way, because of memory usage.  Luckily BizTalk comes with a very nice implementation of streaming Xpath.  This logic is in the XPathMutatorStream (part of the BizTalk.Streaming.dll).

This post shows what Xpath functionality is supported and (more important) what is not supported in this stream.

Usage

I wrote a sample pipeline component, that reads an XML file from disk with a collection of promoted properties and Xpath statements.

<Promotions>

  <Property enabled="false" name="name" propNamespace="ns" xPath="" />

</Promotions>

The component keeps all the Xpaths and properties in a collection and passes the Xpaths to the XPathMutatorStream.  This stream will call a delegate function in the pipeline component, where we will promote the property, linked to that Xpath with the Xpath value.

Code

Collection of properties

We maintain a list of PropertyConfig objects and a reference to the BizTalk message.

class PropertyConfig
{
public string PropertyName { get; set; }
public string PropertyNamespace { get; set; }
public string XPath { get; set; }
}

public class XpathMutatorTester : IComponent, IPersistPropertyBag, IBaseComponent, IComponentUI
{
private List properties = new List();
private IBaseMessage biztalkMessage = null;
}

Execute method

public IBaseMessage Execute(IPipelineContext pContext, IBaseMessage pInMsg)
{

We start by reading the (hardcoded) XML file and interpret it, using LINQ for XML.  Notice that we also add the Xpaths to the XPathCollection object, that will be passed to the XPathMutatorStream.

// Set variables
biztalkMessage = pInMsg;
XmlReader reader = XmlReader.Create(pInMsg.BodyPart.Data);
XPathCollection xpaths = new XPathCollection();
// Load configuration file
var xmlConfig = XDocument.Load(@"d:\Projects\R&D\XPathMutatorStreamExample\Sample.xml");
// Load all properties that are enabled
var propertyList = from p in xmlConfig.Elements("Promotions").Elements("Property")
                   where p.Attribute("enabled").Value == "true"
                   select p;
// Load all properties to collection
foreach (var property in propertyList)
{
    PropertyConfig config = new PropertyConfig()
    {
        PropertyName = property.Attribute("name").Value,
        PropertyNamespace = property.Attribute("propNamespace").Value,
        XPath = property.Attribute("xPath").Value
    };
    properties.Add(config);
    xpaths.Add(config.XPath);
}

The only thing left in this method is changing the stream of the incoming message to the XPathMutatorStream and passing the delegate function that will be called, once an Xpath is fetched.

// Set delegate method that will be called when Xpath is found
ValueMutator mutator = new ValueMutator(handleXpathFound);
// Define the XPathMutator Stream
pInMsg.BodyPart.Data = new XPathMutatorStream(reader, xpaths, mutator);
return pInMsg;

The ValueMutator is the delegate function where the Xpath value gets promoted to the correct promoted property

private void handleXpathFound(int matchIdx, XPathExpression matchExpr, string origVal, ref string finalVal)
{
// Select property config from the configuration, based on the xpath expression
var property = properties.First(p => p.XPath == matchExpr.XPath);
biztalkMessage.Context.Promote(property.PropertyName, property.PropertyNamespace, origVal);
}

Supported Xpath functionality

The sample queries are tested on the following sample XML:

<PurchaseOrder>

  <Header number="1302" deliverydate="1/2/2010"></Header>

  <Party name="Microsoft" type="BY" />

  <Party name="CODit" type="SU" />

  <Lines>

    <Line id="1" amount="39" currency="USD">

      <enabled>true</enabled>

    </Line>

    <Line id="2" amount="29" currency="USD">

      <enabled>true</enabled>

    </Line>

    <Line id="3" amount="31" currency="USD">

      <enabled>true</enabled>

    </Line>

    <Line id="4" amount="9" currency="EUR">

      <enabled>true</enabled>

    </Line>

  </Lines>

</PurchaseOrder>

Basic Xpath queries. 

Queries without functions, sums, conditions are fully supported.

·         /PurchaseOrder/Header/@number

·         /PurchaseOrder/Lines/Line/@amount

Indexed Xpaths.

Indexed Xpaths seem to be supported on the XPathMutator implementation.  Indexed Xpaths are Xpaths that search for an element at a specific position.  The following sample worked.

·         /PurchaseOrder/Lines/Line[3]/@amount

Xpath queries with sub queries. 

Queries with ‘forward where conditions’ in one of the elements, are supported.  The following expression is supported, because the name and the type attribute are on the same element.

·         /PurchaseOrder/Party[@type='SU']/@name

This query is also supported, because the currency attribute in the sub clause is one level higher (and is read earlier) than the enabled element that is being read.

·         /PurchaseOrder/Lines/Line[@currency='USD']/enabled

The following query is not supported, because the enabled element in the sub query is one level down, compared to the currency attribute we are reading.  The exception we get is: Cannot get the child value.

·         /PurchaseOrder/Lines/Line[enabled='true']/@currency[1]

The following query is also not supported, with the same exception (Cannot get the child value).  To be honest, I was expecting this one to work, because we are checking and getting the enabled element , which is on the same level.

·         /PurchaseOrder/Lines/Line[enabled='true']/enabled[1]

Bottom line, we can conclude that sub queries are only supported when the items in the sub query occur before the item that gets selected.

Functions.

It looks like functions are not being supported, which makes sense in some cases (sum, average, total), but which does not make sense in other cases (concat, substring…)

These Xpath queries threw an exception, while adding them to the XPathCollection object.

·         sum(/PurchaseOrder/Lines/Line/@amount)

·         count(/PurchaseOrder/Lines/Line)

·         substring(/PurchaseOrder/Header/@number, 1, 2)

The use of namespaces

For the sake of simplicity, the above sample did not contain any namespaces in the XML, which is uncommon off course.  I always prefer the above notation for Xpaths, compared to this one:

Defining namespace aliases is luckily possible, by using the NamespaceManager on the XPathCollection object:

XmlReader reader = XmlReader.Create(pInMsg.BodyPart.Data);
XPathCollection xpaths = new XPathCollection();
xpaths.NamespaceManager = new XmlNamespaceManager(reader.NameTable);
xpaths.NamespaceManager.AddNamespace("po", "purchaseOrder");

This allows executing the Xpath as follows:

·         /po:PurchaseOrder/po:Header/@number

Disadvantages and risks of the streaming xpath approach

While we all agree that the XPathMutatorStream is well suited to keep memory usage low and to support larger messages, there are quite some pitfalls in using this component in a truly streaming fashion.

-          In the sample, the properties only get promoted, when the stream is being read.  This makes it impossible to read the property in a component, further down the pipeline, when the stream is not yet consumed.

-          It is difficult to check if all Xpaths have been found, because this check should only occur when the message is fully read (which ideally is done by the Messaging Agent).

Using Xpaths in the CODit products

A lot of our products (especially Transco & Matrix) make intensive use of Xpath for lookup, database translation, routing…  (more information can be found here)

We have made the design decision to not use the XPathMutatorStream in our products, because we want to give the full functionality of Xpath to our customers and we prefer functionality before performance. 

All the above Xpaths are supported in our products (including functions) and we also extended the functionality by adding the possibility of concatenating fields and constants, by using the ‘+’ sign.

Conclusion

The XPathMutatorStream is a great class to read basic Xpaths on large messages in pipeline components that are built for specific (pre-known) message types.  To make use of it in generic components with configurable Xpaths, it might lack too much flexibility. 

Sam Vanhoutte, CODit

Sometimes it can be very tricky to deploy a BizTalk application because of assembly dependencies.
But recently I found an easy way to determine these dependencies on a running BizTalk environment.

Imagine the scenario where you have to do a patch of a certain assembly and you get following error.
Cannot update assembly "X.dll" because it is used by assemblies which are not in the set of assemblies to update. 
This also shows a list of dependencies that use this assembly. It's not such a big deal that you get this error. But you don't want this to happen while your customer is watching.  It can also make automated deployment fail.
You can solve this with an easy C# application that creates this list before you try to deploy.

First reference C:\Program Files\Microsoft BizTalk Server 2009\Microsoft.BizTalk.Deployment.dll.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using BTS = Microsoft.BizTalk.Deployment;
using System.Data.SqlClient;

namespace TestApp
{
    class Program
    {
        static void Main(string[] args)
        {
            SqlConnection conn = new SqlConnection("Data Source=(local);Initial Catalog=BizTalkMgmtDb;Integrated Security=SSPI;");
            BTS.DependencyInfo di = new BTS.DependencyInfo();
            BTS.BizTalkAssemblyName btsAssName = new BTS.BizTalkAssemblyName("assemblyname", "1.0.0.0", "neutral", "publicKeyToken");

            foreach (BTS.BizTalkAssemblyName assn in di.GetUsedBy(conn, btsAssName))
            {
                Console.WriteLine(assn.FullName);
            }

            Console.ReadLine();
        }
    }
}

And that is it. You can also call this recursively to determine the entire depency tree. But with this simple code, you can save yourself some time while deploying, or help your automated deployment.

Tim D'haeyer

I had to write a stored procedure that writes the current database size to a text file on a shared network location. This needed to happen each month, and we will use SQL Jobs for that... I didn't find any good solutions on the net so I thought to share this one!

Stored procedure code

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        CODit
-- Create date: 05/01/2010
-- Version: 1.1
-- Description:   Calculate Database Size and write to a file
-- =============================================
CREATE PROCEDURE [dbo].[qry_spaceused_toFile]
 @FullFolderPath varchar(255),
 @DatabaseName varchar(255),
 @SQLServerName varchar(255)
AS
BEGIN
declare @myDate varchar(255),
@QueryString varchar(4000),
@QueryString2 varchar(4000)

Select @myDate = convert(varchar, getdate(), 2)
Select @FullFolderPath = @FullFolderPath + @SQLServerName + '--' + @myDate + '.txt'
Select @QueryString = 'sqlcmd -S"'+@SQLServerName+'" -E -d '+@DatabaseName+' -Q"execute sp_spaceused" >>"'+@FullFolderPath+'" -s"" '
Select @QueryString2 = 'type "'+@FullFolderPath+'"'
create table #errorlog(line varchar(2000))
execute master.dbo.xp_cmdshell @QueryString

insert into #errorlog 

execute master.dbo.xp_cmdshell @QueryString2

Select @QueryString = 'sqlcmd -S"'+@SQLServerName+'" -E -d '+@DatabaseName+' -Q"print CHAR(13)" >>"'+@FullFolderPath+'" -s"" '
execute master.dbo.xp_cmdshell @QueryString
execute master.dbo.xp_cmdshell @QueryString

select line from #errorlog

drop table #errorlog
END

Usage (Make sure your path folder already exists):

exec qry_spaceused_toFile 'PathToFileLocation', 'DataBase', 'SQLServerName'

Possible Output:

database_name       database_size     unallocated space  
------------------------------------------------------------ 
CIPPlatform             6.75 MB             0.81 MB             

reserved          data              index_size        unused            
------------------------------------------------------------
3264 KB           1712 KB           1288 KB           264 KB

Explanation:

We are using the sqlcmd to write to a text file. This has some limitations, but it's fine to use in our situation. I didn't find any easy method to write to a file using sql. If you want to learn more about the sqlcmd utility, I suggest you to read this tutorial. As the name states, sqlCMD, you normally use this command in a command prompt. However, you can use it in SQL by using the master.dbo.xp_cmdshell procedure! We are using the built-in system query 'sp_spaceused' to calculate the space. After executing, you'll see two resultsets. In order to write those 2 resultsets to a file we write the results to a temporary table and write the contents that are stored in the table. The output file name is a concatenation of the database name and date.