Writing Stored Procedures is an art of its own. As all of you know it’s very different from writing ordinary code and presents its own kind of problems and issues when it comes to performance, builds, version control, testing etc. This post will try and highlight a few points that I find important when it comes to Stored Procedures, and the especially in a BizTalk and integration related scenario.

Consider the simplified procedure below, I’ll use is an example for much of the discussion in the rest of the post. Basically the procedure inserts or updates (based on if the optional EmployeeID exists from before or not) employee information to the Employee table of a database named "TestDB".

Use [TestDB]
Go
If Exists(select * From dbo.sysobjects Where id = object_id(N'AddEmployee') And OBJECTPROPERTY(id, N'IsProcedure') = 1) Drop Procedure AddEmployeeGo
Create Procedure AddEmployee
    @EmployeeId Int = -1,
    @LastName Varchar(30),
    @FirstName Varchar(30),
    @MiddleName Varchar(30)
As
Begin
    Declare @currentEmployeeId int
    Declare @currentAction tinyint
    -- Check based on the EmployeeId if the employee exists from before    
    If Not Exists(Select EmployeeId From Employee Where EmployeeId = @EmployeeId)
        Begin
            -- Inserts new employee        
            Insert Into Employee (LastName,FirstName,MiddleName)
            Values (@LastName, @FirstName, @MiddleName)
            Set @currentEmployeeId = Scope_Identity()
            Set @currentAction = 1
        End
    Else
        Begin
            -- Updates employee        
            Update Employee Set LastName = @LastName,
            FirstName = @FirstName WHERE EmployeeId = @EmployeeId
            Set @currentEmployeeId = @EmployeeId
            Set @currentAction = 2
        End      

    Select @currentEmployeeId As [@EmployeeId], @currentAction
    As [@ProcedureAction] For Xml Path ('Response')
End
Go

Builds and version control

I always try to have the complete database version controlled. One solution to this that some people advocate is to have a central development database. I have however always found it to be a lot of hassle with overwriting each others changes and so on and always try to make it possible for each developer to build a local version of the database for development. It should also be possible to always check out the latest version and run it to build all the database objects - no matter what you have from before. This is of course a bit tricky when it comes to the actual tables as you might have data and so on (in my opinion it shouldn’t be the case during development but that’s another post discussing test data for the database etc).

When it however comes to Stored Procedure as in this post it’s easy - drop the old one if it exists and create the new one as in the example.

If Exists(select * From dbo.sysobjectsWhere id = object_id(N'AddEmployee') And OBJECTPROPERTY(id, N'IsProcedure') = 1) Drop Procedure AddEmployee
Go
Create Procedure AddEmployee @EmployeeId Int = -1, ...

If found this article by Jeff Atwood over at Coding Horror (and the articles by K. Scott Allen it links to) very good on how to think about version control database objects.

Idempotent procedures

In complex integration solution it’s a common problem with multiple messages - sending systems might send the same message twice for a number of reasons and one will end up with a shaky system if that causes an error in the integration. I’ve also seen solutions to this problem where a check is done against the database (say from a BizTalk orchestration) and based on that result a decision is made to call an update or an insert procedure. In my opinion that just unnecessary complexity and database communication. I believe one should try and handle whether to update or insert inside the procedure as long as that’s possible and effective.

I in the AddEmployee Stored Procedure example might receive a EmployeeID as an input parameter and based on a check inside the procedure if the employee exists from before with that id a post is either updated or inserted.

Response message

I generally always want two things in return from a Store Procedure like the one in the AddEmployee example (In a select based procedure that would of course be very different as we like all the data in return).

1. The id that been added or changed
2. What action that’s been performed (insert or update)

What action that was performed is a good idea to return as it’s quite usual to use this in for example BAM tracking or other logging and so on (we might want know how many employees that was added versus updated for example).

Using the Xml Path syntax for generating the Xml response (thanks again Nick for a great post) from the procedure has made it even simpler to actually skip using the Add Generated Item option in BizTalk. All you have think about is to set name of the child node the request root node to the same name as the procedure (in this case AddEmployee). Also remember that the type should be set up as a Multi-Part Message Type in the orchestrations you use it from and that the Receive Pipeline you choose in the Send Port must use a Xml Disassembler component (XmlReceive pipeline will do fine unless you have your custom pipeline for some reason).

Error handling

Some people might find it strange that I don’t have any error handling in my Stored Procedures but in most cases an error in the procedure will cause an exception in the port communication with the database and that’s fine. If I’d like to handle that error I’ll handle that either in the orchestration or using other mechanisms in BizTalk like Failed Message Routing etc. If I can’t handle the error in the procedure I don’t see a reason to catch it.

Security

I’ve seen to many cases where developers actually used the user and password login option available on the database adapter and port. Even if the credentials are safe in the SSO database there is a risk you’ll end up in a mess with login data spread all over the BizTalk administration. One should always use try to use Windows NT Integrated Security!

Even more common than the above is to have a user that part of the administrators group or similar to hit the database - don’t! I usually try an set up one Host to run as in all the communication with a specific database (Could also be a Host for that database and that BizTalk Application so that different applications have their own Host). I’ll then give that specific user the least privileges needed in the specific database.

In the AddEmployee and TestDB example I’d create a new Group in the AD called for example "BizTalk TestDB Users". I’d then create a User in the AD called "BizTalk TestDB User". It’s based on this I’d then create a Host in BizTalk referencing the above created Group.

Finally we’ll create a Host Instance and make a Send Handler of it on the SQL Server Adapter.

Now we can set the least required privileges on the "BizTalk TestDB Users" in Sql Server as that the Group the User belongs that’ll hit the database. In this case this means granting the Group to the standard BizTalk required privileges for a Host besides granting it Execute right on the AddEmployee Stored Procedure.

Something I missed? How do you handle your database communication and security in your BizTalk implementations?

Why loose coupling?

There was a question in Microsoft BizTalk forums the other day about how one could implement a Scatter and Gather pattern in a more loosely coupled fashion.

Most examples on the implementation of this pattern in BizTalk use the Call Shape functionality available in BizTalk orchestrations. This however creates a hard coupling between the “Scatter orchestration” and it’s “partner orchestrations”. The downside of that is that when one adds or removes a partner the whole solution has to be recompiled and redeployed.

If one however could use the publish-subscribe architecture in the MessageBox to route messages between the “Gather orchestration” and it’s partners, it’d be possible to add partners without having to worry about the rest of the solution. This post shows and example on how to implement a solution like that.

The BizTalk process in steps

NOTE: Notice the difference between the PartnersRequest, the PartnersResponse, the PartnerRequest and the PartnerResponse messages. The names are unfortunately very similar.       

The PartnersRequest and PartnersResponse messages are used for communication between the Scatter and the Gather orchestrations. It’s also a PartnersRequest message that activates the process.

PartnerResponse and PartnerRequest are used for communicating between the Scatter and the Gather orchestration and all the partner orchestrations.

1. Request and scatter
   A PartnersRequest message is received. This message is an empty message and is only used for activating the process in this example scenario. The PartnersRequest message is consumed by the Scatter orchestration. The Scatter orchestration creates one PartnerRequest message. The orchestration also generates a unique key called a RequestID and start a correlation combining that that id and the PartnersRequest MessageType. Finally it post the PartnerRequest message to message box, writes the generated RequestID to the request messages context (RequestID is a MessageContextPropertyBase based context property) and dehydrates itself.
   
2. Partners
   All the enlisted Partner orchestrations pick up the PartnerRequest message from the message box. These orchestration then communicates with their specific data source (could be a service, database, file or whatever), receives a response. Finally these orchestrations transform the response they received and creates a PartnerResponse message that’s posted to the message box. Notice that the RequestID that was generated by the Scatter orchestration is also part of the context of the newly created PartnerResponse message.
3. Gather
   The PartnerResponse messages are routed to the Gather orchestration. This orchestration uses a Singleton pattern based on the RequestID which all PartnerResponse messages carried with them in their context. This means that it’ll receive all the PartnerResponse messages containing that same RequestID into the same orchestration instance (ergo all the Partners that were activated by the request message being sent from one Scatterer). For each message instance it receives it add it’s price to a total price variable. When the Gather orchestration has received all the PartnerResponse messages (the orchestration knows how many Partners responses it should expect from one Scatterer orchestration and we can timeout if we don’t get all expected with a timeframe) the total price we calculated is written to a PartnersResponse message.
4. Response
   This message is routed back the Scatter orchestration by using the correlation it initialized in the start. It’s finally this orchestration that send the final outgoing message (a PartnersResponse message).

Example solution

An example of the implementation can be downloaded from here.

The solution contains five different schemas.

1. PartnersResponse
   Used for initializing the process.
2. PartnersRequest
   Send from the Gather orchestration to the Scatter orchestration. It’s also the final result and outgoing message from the process.
3. PartnerRequest
   Picked up and activates all enlisted Partner orchestrations.
4. PartnerRequest
   Send from the Partner orchestration containing the result from the Partner Service and send to the Gather orchestration.
5. LooselyCoupledScatterGatherExampleProperties
   Property schema for storing the RequestID and to correlate all the PartnerResponses as well as the final PartnersResponse back to the Scatter orchestration.

Five orchestrations

1. Scatterer orchestration
   The “main orchestration” that receives a request message from outside and “scatters” party requests to all the party orchestrations.
2. Gatherer orchestration
   Gatherer orchestration that gathers all the responses from the partners and transforms these to a reply that is being routed back to the Scatterer and back out.
3. Partner1, Partner2 and Partner3 orchestration
   Partner orchestrations that communicates to different services and receives price information.

Setting up and testing the example solution - it’s easy!

When the solution is built and deployed one needs to setup and bind two ports; one outgoing port and one incoming port (this could also be a Request-Response port by changing the port type in the Scatterer orchestration). That’s it!

Enlist and start everything by dropping a PartnersRequest test message (you’ll find one among the zipped files) in the incoming folder. A PartnersResposne message should then be published in the outgoing folder containing a calculated price from all the Partner orchestrations.

Test message and a binding file are part of the zipped solution.

What would be different in “real life” solution?

I’ve made some major simplifications in this example to make it easy for setting it up and test the concept. These would very different in a “real” solution.

Partner Services
The Partner orchestrations are very simple. They actually don’t communicate with outside world at all. All the do is setting a hard coded price and post a response. In a real solution these would not be part of the same solution as the Scatter and Gather orchestration (otherwise we would be force to redeploy when adding a Partner orchestration to the dll).

The Partner orchestrations would also communicate with some sort of outbound source like a web service or database for example. This would however complicate the setup therefore I’ve skipped that part in the example.

Managing partners
One of the benefits with a loosely coupled implementation is the possibility to add and remove Partner orchestrations without having redeploy the rest of the solution. Using this implementation the Gatherer orchestration needs to know how many Partner responses it should wait for before timing out. This requires that value being set in a config file or something similar. In this example the number of Partners are hard coded into the Gather orchestration (it’s set to 3 Partners) to simplify the setup.

Final thoughts

Knowing how to create loosely coupled solutions like this is good knowledge to have. It’s my own and others belief that this architecture makes it possible to create more robust and separated solutions that one can update without having to do a lot a work and disturb the current processes. It’s however not the best solution performancewise as it adds a lot of extra hits on the MessageBox database and generates more work for the MessageAgent.

There are also a few things to watch out for:

Eternal loops
It easy to end up in a situation where you’re subscribing to the same message as you posting to the MessageBox. That’ll create and endless loop and cause a lot disturbance before you’ll find it. Think through and document you subscriptions!

Correlations for promoting values
When doing a direct post in BizTalk most properties are not promoted. To force you properties to be promoted you’ll have to initialize a correlation on the property as you send it. I can’t say I like this. There should be a other way of saying that one wants it promoted.

A couple of other useful articles as we on the subject:

• Is there a pub/sub system underneath BizTalk?
• Okay, so where do subscriptions come from?
• Starting BizTalk Server 2004 orchestrations using the MessageAgent.

Download the example orchestration and let me know how you used it and what your solution looks like!

Whoho! I’ll be speaking at Developer Summit 2008. Developer Summit is a conference hosted by Cornerstone, it’s being held in Stockholm between the 9th and 11th of April 2008. 

I’ll be doing a talk on Master Data Management (MDM) for the Enterprise using BizTalk 2006. Basically I’ll present some basic theory behind the MDM concept and how it relates to SOA. Then I’ll relate all that to a customer case I recently worked on solving a MDM requirement. Finally I’ll be showing a short demo where I publish some Master Data and update all subscribers to it. In the same demo I’ll also demonstrate on how to monitor the process using Business Activity Monitoring.

Sound interesting? Something specific you think should be in the presentation? Let me know!

Hope to see you there!

Way to often I get a request to deliver a message from BizTalk without any XML namespace. Comments like the one below aren’t that rare.

Why do you put those "ns0" I front of every tag!? We can’t read the XML when it’s written like that! All our XPath and XSLT seems to fail.

We don’t want <ns0:OutMessage xmlns:ns0="Acme.Messages.OutMessage/1.0"> … We expect the messages from you to be like <OutMessage> …

Is that some BizTalk specific?

I always try to explain what XML namespaces are, and why it’s a good idea to use them (especially when it comes to versioning of messages). Sometimes it’s just impossible to get people to understand the advantages of using it and to persuade them to change their solutions to handle XML namespaces. It’s in these cases it’ll be up to the implementation in BizTalk to remove the namespace.

How to remove XML namespace

There are a couple of ways of achieving this, we can use .NET code that we call in a pipeline or in an orchestration. But we can also handle this using XSL - and that’s what I’ll show in this post. The XSL stylesheet below will remove all XML namespaces while transforming the message. Basically it just copies the nodes, attributes and it’s values using the local-name() function to ignore the XML namespaces.

<?xml version="1.0" encoding="utf-8"?>

<xsl:stylesheet version="1.0"
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

    <xsl:template match="/">
        <xsl:copy>
            <xsl:apply-templates />
        </xsl:copy>
    </xsl:template>

    <xsl:template match="*">
        <xsl:element name="{local-name()}">
            <xsl:apply-templates select="@* | node()" />
        </xsl:element>
    </xsl:template>

    <xsl:template match="@*">
        <xsl:attribute name="{local-name()}">
            <xsl:value-of select="."/>
        </xsl:attribute>
    </xsl:template>

    <xsl:template match="text() | processing-instruction() | comment()">
        <xsl:copy />
    </xsl:template>

</xsl:stylesheet>

Apply the XSL transformation

We can use this XSL stylesheet in a ordinary BizTalk map on the send port using the Custom XSL Path property in the BizTalk Mapper. The result is that the XSL we usually generate in the mapping tool will be overridden by our own XSL stylesheet. The figure below shows how we use the property windows of the grid in the BizTalk Mapper to set the property and point the Mapper to our XSLT document.

But what if we already have a map on the send port and it’s that already transformed message we like to remove the namespace from? One possibility is to use the XSLT Transform pipeline component that comes with the BizTalk 2006 SDK. It’s usually located at C:\Program Files\Microsoft BizTalk Server 2006\SDK\Samples\Pipelines\XslTransformComponent\XslTransform on your development machine. I’ve written about this sample component before here were I used it another scenario.

The figure below shows how we use the property windows of the XSLT Transform Component in a pipeline in the Pipeline designer tool to set the path to our XSLT stylesheet.

Final thoughts

The XSLT Transform component is far from perfect and the obvious problem is of course that the component loads the whole message into memory using the XmlDocument class to read the message. That means that this solution isn’t for those scenarios where you’ll have huge messages coming in by the thousands. But for those cases where you have normal sized messages and you have a idea of the traffic you receive, it’s a quick and easy solution.

Any comments on the pro and cons on this solution and how you usually solve this scenario will be appreciated!

UPDATE #1: Make sure you don’t miss Johan Hedbergs solution to this problem. Basically he solved it using the Microsoft.BizTalk.Streaming.dll which will give you  better memory management.

UPDATE #2: Based on the comments to this post I’ve posted an "updated" post here solving this using ordinary mapping and defining a schema without a XML namespace - easier and more correct. 

cXML (commerce eXtensible Markup Language) is a XML based standard for communication of data related to electronic commerce. The problem from a BizTalk perspective is that they don’t publish any XML schemas (XSD),  only Document Type Definition (DTD).

When trying to generate a schema based on a DTD using the functionality in BizTalk (via Add Generated Items) one ends up with a schema split of three files that really doesn’t make any sense (XmlSpy doesn’t do a very good job either …). So after a while I found Nick Heppleston schema repository! After some tweaking I actually had a cXML Order schema in the version I was looking for! Thanks Nick!

The next set of problems was to handle the lack of XML namespace and the DOCTYPE declaration that messages validating against DTD carries on top.

<?xml version="1.0" standalone="no"?>
<!DOCTYPE cXML SYSTEM "http://xml.cxml.org/schemas/cXML/1.2.014/cXML.dtd">
<cXML xml:lang="en-US" payloadID="2007117.25919@Contempus" timestamp="2007-11-07T11:06:16+01:00">

To handel these two issues I set up a receive pipeline that looked like the one below.

Remove the DOCTYPE declaration

First I created a pipeline component to remove the DOCTYPE node. It’s simple code using regular expression to find the DOCTYPE node, replace it with an empty string and return the message.

public IBaseMessage Execute(IPipelineContext pc, IBaseMessage inmsg)
{
   string messageString = new StreamReader(inmsg.BodyPart.Data).ReadToEnd();
   Regex doctypePattern = new Regex("<!DOCTYPE.+?>");
   messageString = doctypePattern.Replace(messageString, string.Empty);
   MemoryStream memStream = new MemoryStream();
   byte[] data = Encoding.UTF8.GetBytes(messageString);
   memStream.Write(data, 0, data.Length);
   memStream.Seek(0, SeekOrigin.Begin);
   inmsg.BodyPart.Data = memStream;
   return inmsg;
}

Set an XML namespace

Secondly I used Richard Seroter’s post on how to change the SetNSForMsg component to add a XML namespace. That’s the second component showing in the decode stage of the pipeline.

Arrow number 3 shows how the SetMsgNS exposes a property that allows us to set the namespace that we can configure per pipeline. In this case I’ve set it to http://schemas.modhul.com/cXML/1.2.014/OrderRequest which is the namespace of the cXML schema I’m currently working agains.

In the end we’ll have a message with the following declaration and root node.

<?xml version="1.0" encoding="utf-16" standalone="no"?>
<cXML xml:lang="en-US" payloadID="2007117.25919@Contempus" xmlns="http://schemas.modhul.com/cXML/1.2.014/OrderRequest" timestamp="2007-11-07T11:06:16+01:00">

Now we’re ready to start mapping!

Ordered Delivery is a great feature of BizTalk 2006. The problem is however that as soon as one introduce a orchestration in the process it doesn’t work. The Ordered Delivery option on ports in BizTalk 2006 ensures two things today:

1. Messages are published to the MessageBox in the same order as they were consumed by the Receive Port.
2. Messages are consumed from the MessageB0ox and published to the end destination by the send port in the same order as they were persisted in the MessageBox.

What’s the problem?

Fine. But if we introduce an orchestration each message being persisted in the  the MessageBox will start it’s own orchestration instance. These instances could (and in many cases will) finish in different order than they started. This means that we loose the correct order of the messages one they are persisted back to the MessageBox from the orchestration instance.

The above “limitation” is well known and the solution to this has always been a Sequential Orchestration pattern (also called Singleton Orchestration). Basically this correlates messages from for example a specific Receive Port of a particular Message Type. This ensures that only one instance of the orchestration will be started and we can keep the order of the messages. We’ve experienced a lot of problems using this this kind of solution for Ordered Delivery - all from Zombies to them using huge amounts of memory and so on.

Microsoft’s new solution

Now it finally looks like this problem is about to be solved! Microsoft released a white paper recently (download it here - found it via Richard Seroter’s blog) to solve these problems in a other way. It basically means that a pipeline will stamp the messages with a sequence number as they enter BizTalk (the messages are then in order as we’re then using Ordered Delivery on the port). Then one can have as many orchestration as one like processing these messages and publish them back to the MessageBox (now possibly out of order but with a sequence number stamped on them). The last business orchestration will set a “destination” property that will route the message to a resequence orchestration. This orchestration resequences the messages and decides by checking the last sequence number it sent out if the current message should be sent out or put back on queue.

Conclusion

My biggest concern in this solution is that is still based on a singleton. We’ve had cases where where the send procedure been extremely slow (for example when we used SOAP and had a slow Web Service in the other end), then the orchestration has built up in memory as it queued messages internally. However it looks like this solution is well thought through with a “flush queue” functionality, stop message, some ideas on how to handle errors (remember that if the singleton fails and you don’t handle this your processes stop) and so on.

Read it and please let me know what you think!

I’ve written about the ESB concept before and what I think an ESB architecture is. In the posts comments there were some discussion about if BizTalk is an ESB or not. And if not - why not?

I think this article does a great job in explaining and discussing this subject. Basically it says that the main reason for BizTalk not qualifying as an ESB today (I know about the ESB Guidance - we get there ;)) is because of it’s “all-or-nothing” packaging. What that means is that it’s different functionality can not be separately deployed a cross a bus structure. For example; the scenario of having the transformation functionality in one place and the routing functionality in another just isn’t possible in today’s architecture. Today you install the full product in one place.  

I think I’ve personally have learnt to live with this limitation. On the other hand I can see that the possibility of splitting parts up definitely changes things as the possibility of reuse and single point of failure problems etc.

Is BizTalk going the ESB way?  

I still haven’t had as much time as I’d like to examine the ESB Guidance but I look forward to see how they worked around the problem described above.

Just looking at this architecture image shows that they’ve split things up in a new way and that each part is accessible trough services - nice! Could this be the future architecture of BizTalk server? What do you think? 

I’ll try and install the ESB project as soon as I get some more time on my hands. In the mean time I’d love some tips and comments on articles and other reading on the experiences of the ESB Guidance project.  

There was a question on the Microsoft newsgroup the other day where someone had to split a message into parts. But some of the information that was supposed to go in to the different parts were part of the envelope. I thought I’d give my solution to the problem a try - here it is.

Say for example that we receive the following message.

<ns0:EmployeeSalesReport ReportID="R100" EmployeeID="0012345" xmlns:ns0="http://Example.TransformAndSplit.EmployeeSalesReport">
    <Sales>
        <ns1:Sale xmlns:ns1="http://Example.TransformAndSplit.Sale">
            <ItemID>100</ItemID>
            <Amount>10</Amount>
        </ns1:Sale>
        <ns1:Sale xmlns:ns1="http://Example.TransformAndSplit.Sale">
            <ItemID>200</ItemID>
            <Amount>20</Amount>
        </ns1:Sale>
    </Sales>
</ns0:EmployeeSalesReport>

In the above message we have different sales information from one employee but all the global employee and report information (the ReportID and EmployeeID attributes) exists in the root node (what we’ll call the envelope of the message).

What we like to achieve is to split this envelope message into it’s different sale item so we get separate message looking something like the below where each item contains the global information from the envelope.

<Sale xmlns="http://Example.TransformAndSplit.Sale">
    <ItemID xmlns="">200</ItemID>
    <Amount xmlns="">20</Amount>
    <EmployeeID xmlns="">0012345</EmployeeID>
    <ReportID xmlns="">R100</ReportID>
</Sale>

There might be different needs for doing this in a pipeline but much of it comes down to choice of architecture style in your BizTalk solution. Personally I’d refuse to introduce another orchestration for splitting a message like this. I don’t believe that’s what orchestrations are for (they should deal with possible logic and workflow in my world). I’m sure other people feel different - feel free to use the comments.

Step 1 - Transforming the envelope message using XSLT

The first thing we’ll need to do is to transform the envelope message so that the information from the root node (the ReportID and the EmployeeID) get into every single Sale-node. There are a couple of ways of achieving this but I’ll use the XSLT-transformation pipeline component that ships with the BizTalk 2006 SDK.

All this component does is to let you point out a XSLT stylesheet you like to use to transform your message.

The stylesheet I’ve used is part of the solution that you can download here. I will not show the stylesheet full script here but rather the result after the message travel through the component.

<?xml version="1.0" encoding="utf-8"?>
<EmployeeSalesReport EmployeeID="0012345" ReportID="R100" xmlns="http://Example.TransformAndSplit.EmployeeSalesReport">
    <Sales xmlns="">
        <Sale xmlns="http://Example.TransformAndSplit.Sale">
            <ItemID xmlns="">100</ItemID>
            <Amount xmlns="">10</Amount>
            <EmployeeID xmlns="">0012345</EmployeeID>
            <ReportID xmlns="">R100</ReportID>
        </Sale>
        <Sale xmlns="http://Example.TransformAndSplit.Sale">
            <ItemID xmlns="">200</ItemID>
            <Amount xmlns="">20</Amount>
            <EmployeeID xmlns="">0012345</EmployeeID>
            <ReportID xmlns="">R100</ReportID>
        </Sale>
    </Sales>
</EmployeeSalesReport>

Ok, nice. Now we have all the information we like in each of the Sale-nodes! All we have to do now is to split the message!

Step 2 - Splitting the message

We’ll use the standard XmlDisassembler component for splitting the message. All we have to do is to make sure we done the following.

1. Set the schema envelope property to “true” in the schema editor. 
2. Set the Body Xpath property on the EmployeeSalesReport-node to point to the Sale-node.
3. Configure the Document Schemas and Envelope Schemas (same properties are called DocumentSpecNames and EnvelopeSpecNames if your doing the configuration after deployment in the administration console) properties of the XmlDisassember component to match the names of your schemas.

This is what the envelope schema looks like in this example.

This is what the XmlDisassembler configuration looks like.

There, we’re done! Now we can drop a test file containing two Sale-nodes and all the report/employee information on the top and have the two following separate files as a result when using a file send port.

Setting up the test solution

1. Download a zipped version of the solution.
2. You’ll have to unzip the project to C:\Example.TransformAndSplit. If that isn’t possible you’ll have to change the path to the XSLT stylesheet in the XsltComponent in the pipeline as this is a fixed path.
3. When you build the XsltComponent the Output path is set to the Pipeline Components folder. This assumes that BizTalk is installed at C:\Program Files\Microsoft BizTalk Server 2006\. If that isn’t the case make sure to change the output path of the component.
4. If you’d like to run the pipeline component in debug I’ve set this up using the pipeline.exe tool. There are however some paths in the debug setting of the XsltComponent project also that assumes that your BizTalk solutions is installed at C:\Program Files\Microsoft BizTalk Server 2006\. If this isn’t the case you’ll have to change some values in the Command lines arguments property, but that only if you like to run debug.
5. Build and deploy.
6. Set up a receive port and location using the TransformAndSplitSales pipeline.
7. Set up a send port with for example a filter on the name of the Receiveport you’ve just set up.
8. Drop the example message that’s part of the solution!

Final thoughts

This example didn’t really take much more than an hour to put together. I do however realize that this is a simplified incoming message and that in the case of a more complex message we’d get a messy XSLT stylesheet to maintain. It’s also important to remember that using this method with XSLT transformation means that we will load the entire incoming Xml document into memory, so when we’re dealing with bigger Xml documents we’ll have manipulate the incoming message using other techniques.

Hope this is useful for someone!

The main problem I have with developing BizTalk orchestrations is the fact that I’m so blind when it comes to follow the runtime processing. Using the debugger that is part of the HAT tool is slow and clumsy which IMHO makes the tool almost useless in everyday development. But there is hope!

DebugView

Sysinternals (Windows Sysinternal now - Microsoft bought them last year) DebugView is a wonderful little tool and is especially useful when it comes to figure out what’s actually going on inside an orchestration. Basically the tool listens to system wide debug output. From an orchestration it’s possible to write debug information using the .NET System.Diagnostics namespace and the Debug or Trace class.

Decide on how to filter

There are a couple of handy little tricks that makes DebugView a even better in BizTalk development. First one should try and have something in the debug messages that makes it possible to filter and distinct one’s own (as DebugView listens system wide debug output all running applications debug info will show up). Our team decided on “Sogeti” (our company name) for all our development and to have a method in our BaseLibrary component that outputs something like the below (the BaseLibrary is a small little .NET component with a couple of very useful classes we use company wide in our BizTalk related development).

System.Diagnostics.Debug.WrtieLine("Sogeti, your debug/trace message here")

This make is possible to have a filter in DebugView and to for example have it look something like this.

Trace full context of messages

Another little useful trick is to trace the full context of messages. This is done be storing the message in a XmlDocument typed variable and get the OuterXml property of that variable. The below code is and example of this.

tempXml = msgFindPartyRequest.parameters;
System.Diagnostics.Trace.Write(System.String.Concat("Sogeti, msgFindPartyRequest: ", tempXml.OuterXml));

Example of a full message trace.

To Trace or Debug - that’s the question

As stated earlier both System.Diagnostics.Debug and System.Diagnostics.Trace has methods (Write, WriteLine and so on) for outputting debug information. However there is only one that stays in your compiled code when switching from Development to Deployment compilation mode (guess which one ;)). So make sure you choose the right class for the right information. I like to have some critical messages left using Trace and be able to trace these even on the test and production server.

DebugView on a remote desktop

When running DebugView on an other server (say a test or a production server) using Remote Desktop I’ve found that ones has to use the console user on the server. This kind of makes sense as if we’re connection “normally” we’re creating a virtual session and that’s not were the debug information is written to.

Microsoft SOAP Toolkit 3.0

This is a totally other tool than DebugView but I thought it fit here any way. It’s a handy tool when working with SOAP based messages. Without it’s very hard to actually figure out how the raw request and response message look and why your orchestration web service is acting the way it does.

The trace tool is placed as a reverse proxy between BizTalk and the Internet. It’s setup by telling the tracing tool which localhost port to listen at (for example 9091 as in the example below) then we’ll redirect to that port by changing the setting in the BizTalk send port.

Finally we’ll set up the trace tool to listen to port 9091 and redirect all traffic to our web service URL at port 80 in this case. So basically the trace tool will catch all the traffic hitting the 9091 port and forward it.

That’s it! This is probably basic stuff for most of you but hopefully it’s useful for someone! 

I’ve also noticed that the SOAP Toolkit is deprecated by Microsoft and I’d like to hear if anyone used something else (like Fiddler example) for tracing SOAP messages. I’d also love some other tips, tools and methods you use for debugging BizTalk orchestrations.

The XPath function that’s available directly inside BizTalk orchestration is a powerful little tool. However I’ve seen a couple of project where developers just grown tired of it and started creating their own little libraries instead. I’ll be the first to admit that the XPath function isn’t perfect, and it sure doesn’t work like most of the other XPath engines (which is the biggest problem) but it’s still inside the orchestration and you can use it to both read and assign values to a message which is super useful! Basically I don’t see a valid reason for bringing more complexity into your solution by adding another library - as long as you’re just going to read or set value using XPath.

However there is one trick that you should know of when it comes to reading a text value from a node. Basically you have to use both the string() and
text() XPath functions. Both Charles Young and Yossi Dahan has good post on this subject. Also if your new to writing XPath expressions for complex schemas with loads of namespaces and stuff (like schemas in BizTalk) this post could be useful for you.

Finally a nice tool for writing and testing small XPath expression inside Visual Studio (if you don’t want to spend x minutes waiting for XmlSpy to start up …) is XPathmania. Read about it here - I use it all the time!