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

<< PLEASE READ THE COMMENTS SECTION FOR MORE ACCURATE DETAILS >>

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.

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

Dowload the sample here.