3 ways of programmatically extracting a message body from the BizTalk tracking database

post by ThiagoAlmedia

I know often people run into the need to extract a message from the BizTalk tracking database. I thought I’d write a post on the 3 methods I’ve found so far over time.
As some of you know the body and context of messages in BizTalk are compressed, and rightfully so. The actual compression and decompression code is hidden inside a dll called BTSDBAccessor.dll. This DLL, the “BizTalk Database Acessor”, is unmanaged and does a lot of work for BizTalk including accessing the BizTalk databases to send and receive messages.
Based on blog sites and forums I’ve run into over time I know of three ways we can programmatically get a message body out of the tracking database: Operations DLL, SQL, and WMI. I’ve created a little test C# Windows application that uses all three.

The application has only one form (print-screen below), and expects the following parameters:
- The message guid of the message you want to extract.
- The extraction type (Use Operations DLL, Use SQL, Use WMI)
- Tracking DB server (the BizTalk server name)
- Tracking DB name (the BizTalk Tracking database name)

Here is the code for the “Get Message” button:

        private void btnGetMessage_Click(object sender, EventArgs e)

        {

            txtMessage.Clear();

            txtMessage.Refresh();

            GuidmessageGuid;

            try

            {

                messageGuid = new Guid(txtGuid.Text);

            }

            catch (ExceptionexGuid)

            {

                txtMessage.Text = "Please enter a valid Guid. Error: "+ exGuid.Message;

                return;

            }

            switch(cboGetType.SelectedIndex)

            {

                case 0:

                    txtMessage.Text = GetMessageWithOperations(messageGuid);

                    break;

                case 1:

                    txtMessage.Text = GetMessageWithSQL(messageGuid);

                    break;

                case 2:

                    txtMessage.Text = GetMessageWithWMI(messageGuid);

                    break;

                default:

                    break;

            }

        }

Now, let’s finally see the three ways of getting to the message body, shall we?

1. My favourite, use the Microsoft.BizTalk.Operations dll. This is pretty straight forward, you add a reference to Microsoft.BizTalk.Operations.dll and use the GetTrackedMessage of the BizTalkOperations class. You can also get to the message context using this method. This is only for BizTalk 2006 and later. Here is the code:

        //Retrieves the message using the operations DLL - Add Microsofr.BizTalk.Operations.dll to references

        public string GetMessageWithOperations(GuidMessageInstanceId)

        {

            try

            {

                TrackingDatabasedta = new TrackingDatabase(txtTrackingDBServer.Text, txtTrackingDBName.Text);

                BizTalkOperations operations = new BizTalkOperations();

                IBaseMessagemessage = operations.GetTrackedMessage(MessageInstanceId, dta);

                string body = string.Empty;

                using (StreamReaderstreamReader = new StreamReader(message.BodyPart.Data))

                {

                    body = streamReader.ReadToEnd();

                }

                return body;

            }

            catch (ExceptionexOp)

            {

                return "Failed to get message with id "+ MessageInstanceId.ToString() + " from tracking database: "+ exOp.Message;

            }

        }

Here’s the great post mentioning this method by Richard Hallgren:
http://www.richardhallgren.com/reading-the-message-body-and-context-from-the-biztalkdtadb-using-operations-library-in-biztalk-2006/
2. Use the WMI MSBTS_TrackedMessageInstance.SaveToFile method to save the instance to disk. This was the popular method in BizTalk 2004 since there was no operations dll then. Here is the code:

        //Uses WMI to save the tracked message out to a file folder using MSBTS_TrackedMessageInstance class

        public stringGetMessageWithWMI(GuidMessageInstanceId)

        {

            try

            {

                // Construct full WMI path to the MSBTS_TrackedMessageInstance using the message guid (NOTE: MessageInstanceID string value must be enclosed in {} curly brackets)

                string strInstanceFullPath = "\\\\.\\root\\MicrosoftBizTalkServer:MSBTS_TrackedMessageInstance.MessageInstanceID='{"+ MessageInstanceId.ToString() + "}'";

                // Load the MSBTS_TrackedMessageInstance

                ManagementObject objTrackedSvcInst = new ManagementObject(strInstanceFullPath);

                // Invoke "SaveToFile" method to save the message out into the specified folder

                objTrackedSvcInst.InvokeMethod("SaveToFile", new object[] {Application.StartupPath});

                //Get all files in the directory starting with this messageid

                string[] files = Directory.GetFiles(Application.StartupPath, "{"+ MessageInstanceId.ToString() + "*.*");

                string message = "";

                foreach (string file in files)

                {

                    if(file.EndsWith(".out"))

                    {

                        using (StreamReadersr = new StreamReader(file))

                        {

                            message = sr.ReadToEnd();

                        }

                    }

                }

                foreach (string file in files)

                {

                    System.IO.File.Delete(file);

                }

                if (files.Length == 0)

                {

                    throw new Exception("No files found on folder that match the GUID");

                }

                return message;

            }

            catch (ExceptionexWMI)

            {

                return "Failed to save tracked message with id "+ MessageInstanceId.ToString() + " into folder " + Application.StartupPath + ": "+ exWMI.Message;

            }

        }

http://kentweare.blogspot.com/2007/05/biztalk-retrieving-tracked-messages.html

3. The bts_GetTrackedMessageParts stored procedure inside the tracking database expects the message GUID and will return the compressed message data back. We can then use reflection to invoke the Decompress method of the Microsoft.BizTalk.Message.Interop.CompressionStreams class inside Microsoft.BizTalk.Pipeline.dll to decompress the data returned from SQL. Here is the code:

        //Calls BizTalk stored procedure to retrieve compressed message and decompresses it

        public stringGetMessageWithSQL(GuidMessageInstanceId)

        {

            try

            {

                //Connection to DTA database on localhost

                SqlConnection con = new SqlConnection("Data Source=" + txtTrackingDBServer.Text + ";Initial Catalog=" + txtTrackingDBName.Text + ";Integrated Security=True");

                string message = "";

                try

                {

                    SqlCommandcmd = new SqlCommand();

                    SqlDataReader reader;

                    //Build execution of stored procedure bts_GetTrackedMessageParts

                    cmd.CommandText = "bts_GetTrackedMessageParts";

                    cmd.CommandType = CommandType.StoredProcedure;

                    SqlParameterguidParameter = new SqlParameter("@uidMsgID", SqlDbType.UniqueIdentifier);

                    guidParameter.Value = MessageInstanceId;

                    cmd.Parameters.Add(guidParameter);

                    cmd.Connection = con;

                    con.Open();

                    reader = cmd.ExecuteReader();

                    //Get the reader to retrieve the data

                    while (reader.Read())

                    {

                        //Use memory stream and reflection to get the data

                        SqlBinarybinData = new SqlBinary((byte[])reader["imgPart"]);

                        MemoryStream stream = new MemoryStream(binData.Value);

                        AssemblypipelineAssembly = Assembly.LoadFrom(string.Concat(@"C:\Program Files\Microsoft BizTalk Server 2006", @"\Microsoft.BizTalk.Pipeline.dll"));

                        TypecompressionStreamsType = pipelineAssembly.GetType("Microsoft.BizTalk.Message.Interop.CompressionStreams", true);

                        StreamReader st = new StreamReader((Stream)compressionStreamsType.InvokeMember("Decompress", BindingFlags.Public | BindingFlags.InvokeMethod | BindingFlags.Static, null, null, new object[] { (object)stream }));

                        message = st.ReadToEnd();

                    }

                }

                finally

                {

                    con.Close();

                }

                return message;

            }

            catch (ExceptionexSQL)

            {

                return "Failed to get message with id "+ MessageInstanceId.ToString() + " from tracking database: "+ exSQL.Message;

            }

        }

This is the post that I ran into that helped me with this method:
http://www.tech-archive.net/Archive/BizTalk/microsoft.public.biztalk.general/2007-05/msg00124.html

Hope this helps someone! Post a comment (and fill out the email address field) or use the “Contact” page if you want the source code, but it’s pretty much all here in this post.

Dowload the sample here.

More Info: 

Regarding the message context, it is actually possible to get this from the database. I’ve never found it documented anywhere, but by working back through the BizTalk code using Reflector I was able to figure it out:

First add references to Microsoft.BizTalk.Interop.Agent.dll and Microsoft.BizTalk.Pipeline.dll and use the following namespaces:

using Microsoft.BizTalk.Agent.Interop;
using Microsoft.BizTalk.Component.Interop;
using Microsoft.BizTalk.Message.Interop;

Then call the stored proc ops_LoadTrackedMessageContext and then get the result into a MemoryStream as in the code above.

You can then walk through the context as follows:

MemoryStream stream = … // result of calling ops_LoadTrackedMessageContext
IBaseMessageContext context = ((IBTMessageAgentFactory)((IBTMessageAgent)new BTMessageAgent())).CreateMessageContext();
((IPersistStream)context).Load(stream);
for (int i = 0; i < context.CountProperties; ++i)
{
string propName;
string propNamespace;
object propValue = context.ReadAt(i, out propName, out propNamespace);
System.Console.Out.WriteLine(propNamespace + “, ” + propName + “: ” + propValue.ToString());
}

I use a combination of the third method, Alister’s method to get the context (modified to create XML) and my own SQL addition. I make only one call to the database and close it immediately after. This is much more acceptable for us as our production database is pounded with transactions and having too many SQL connections open is a bad thing.

1. I use the following SQL in a stored proc with a dynamic WHERE clause (the following SQL is modified to not be dynamic) and fill a DataTable with the results.
2. I iterate through the rows, saving the message and the context where they are not DBNULL.

This could probably be further optimized, but as we don’t use this often (maybe once in four months), it works for us.

SQL:
SELECT
sf.[Service/Name] AS [Name]
,sf.[ServiceInstance/StartTime] AS [StartTime]
,sf.[ServiceInstance/EndTime] as [EndTime]
,sf.[ServiceInstance/State]
,mioe.uidMessageInstanceId AS [MessageID]
,tp.imgPart AS [Message]
,ts.imgContext as [MessageContext]
FROM
[BizTalkDTADB]..dtav_ServiceFacts sf WITH (READPAST)
LEFT JOIN [BizTalkDTADB]..dta_MessageInOutEvents mioe WITH (ROWLOCK READPAST)
ON sf.[ServiceInstance/InstanceID] = mioe.uidServiceInstanceId
AND sf.[ServiceInstance/ActivityID] = mioe.uidActivityId
LEFT JOIN [BizTalkDTADB]..btsv_Tracking_Parts tp WITH (READPAST)
ON mioe.uidMessageInstanceId = tp.uidMessageID
LEFT JOIN [BizTalkDTADB]..btsv_Tracking_Spool ts WITH (READPAST)
ON tp.uidMessageID = ts.uidMsgId

Alister’s code slightly modified:
MemoryStream ms = new MemoryStream(binContext.Value);
IBaseMessageContext mc;
mc = ((IBTMessageAgentFactory)((IBTMessageAgent)new BTMessageAgent())).CreateMessageContext();
IPersistStream per = ((IPersistStream)mc);
per.Load(ms);

StringBuilder xml = new StringBuilder();

xml.AppendLine("<MessageInfo>");
xml.AppendFormat("<ContextInfo PropertiesCount=\"{0}\">", mc.CountProperties);
xml.AppendLine();
string name;
string ns;
string value;
for (int i = 0; i < mc.CountProperties; i++)
{
mc.ReadAt(i, out name, out ns);
value = mc.Read(name, ns) as string;
xml.AppendFormat("<Property Name=\"{0}\" Namespace=\"{1}\" Value=\"{2}\" />", name, ns, value);
xml.AppendLine();
}
xml.AppendLine("</ContextInfo>");
xml.Append("</MessageInfo>");

}

}