C# solution to decompress BizTalk messages and their context

As most of you will know BizTalk messages and context are stored in the MessageBox and the Tracking database as Image types. By several people a number of posts have been written about decompressing BizTalk messages and their context and here's another one!

When we had an incident with quite some suspended (not resumable) instances, we needed to retrieve message content and context. This information should be used to take appropriate actions for damage elimination.

After a search I found this great article from Thiago Almeida. It even contains a sample solution! Good news though for an intermediate developer as me!
The last couple of days I've been working on the solution and made it use just the SQL method. My goal was to create functions in C#, that would decompress the fields that contains the message and the context and enable the user to use these functions in SQL queries they entered in a textbox.

It ended up with the screen you find below.

Click the image to enlarge it

After selecting the database server which contains the BizTalk databases, you select the MessageBox database. In the Query textfield you can enter your SELECT query. As you might have noticed already, there are 2 non-SQL functions in the currently entered query, namely:

• @MessageToText() - used to decompress messages
• @ContextToText() - used to decompress message context

These are the functions I designed to decompress the message content and context. Before the SQL query is fired at the database, the application will strip these functions (and their optional parameters) for later use, when the resultset is returned from the database.
So basically all you have to do is write a SQL query and when you have a field which contains message content or message context, surround that field with the appropriate function to have the message content or context returned from you MessageBox!

Since messages can be pretty large and you might not need the entire XML from it, or maybe you are not interested in all the context properties, I extended the functions to show just certain XML and/or certain properties from the message context.

Show just a part of the XML message
When you just need a certain part of the XML, you can enter a XPath query and the message will return just that XML. When the message doesn't contain the nodes, elements or attributes mentioned in th XPath query, an empty string is returned.

The @MessageToText() function than becomes called as follows:

@MessageToText([field that contains the message];"[XPath query]")
Example: @MessageToText(imgPart;"//CustomerInfo")

Show just certain context properties
When you just need certain context properties, you can select them by entering them in a semicolon seperated list, like this:

@ContextToText([field that contains the message context];[Context property];[Context property])
Example: @ContextToText(imgContext;MessageType; PortName; OrderType)

You can enter as many or little properties you need. Entering non existant (or typos) properties has no negative side effects.

Other features
Further I built a couple of small features.

• Just like in SQL Server Management Studio (SSMS), when you select a part of the query, only that part is fired at the database.
• When you have a decompressed a message and want to create a XPath query, you can copy the XML from the message, hit the Parse XML... button, paste the XML in the dialog that appeared and experience with the XPath query. When you're done testing the XPath query, you can copy it, close the dialog and paste the XPath at the appropriate position in the Query field.
• To get a 'clean' resultset, you can choose to hide the Outer XML tag and/or the name of the context properties.
• You can choose whether you want to see the field names in the resultset.
• For better readability you can add whitespace between the records in the resultset
• Word wrap is supported in the Output box
• To protect you and your MessageBox from heavy load, only the top xx number of records will be returned, depending on the value selected in the dropdown box.

Recommendations on accessing the BizTalk databases
There are a couple of guidelines when accessing the BizTalk databases directly from SSMS:

• Only do this when no other options are available
• Just do SELECT queries
• Don't forget the WITH(NOLOCK) hint
• Never change existing BizTalk tables, indexes, triggers, SP's, etc...

If you call in Microsoft for help and they find modified BizTalk objects, you will be fully charged for fixing the problems.

Enjoy the tool and if you have any questions or comments, don't hesitate!

Here's the download: BTSDecompress solution

Prerequirement
The decompressing is done by the so called BTS Accessor. This standard BizTalk DLL needs to be installed on the machine where you run the decompress tool.

@Thiago: thanks for having me use your solution!