Biztalk suspended messages in database

Question

I was wondering if someone knows where I can see the data of a suspended message in the biztalk database.

I need this because about 900 messages have been suspended because of a validation and I need to edit all of them, resuming isn't possible.

I know that info of suspended messages are shown in BizTalkMsgBoxDb in the table InstancesSuspended and that the different parts of each message are shown in the table MessageParts. However I can't find the table where the actual data is stored.

Does anyone have any idea where this can be done?

Answer 1

I found a way to do this, there's no screwing up my system when I just want to read them.

How I did it is using the method "CompressionStreams" using Microsoft.Biztalk.Pipeline.dll.

The method to do this:

    public static Stream getMsgStrm(Stream stream)
    {
        Assembly pipelineAssembly = Assembly.LoadFrom(string.Concat(@"<path to dll>", @"\Microsoft.BizTalk.Pipeline.dll"));
        Type compressionStreamsType = pipelineAssembly.GetType("Microsoft.BizTalk.Message.Interop.CompressionStreams", true);
        return (Stream)compressionStreamsType.InvokeMember("Decompress", BindingFlags.Public | BindingFlags.InvokeMethod | BindingFlags.Static, null, null, new object[] { (object)stream });
    }

Then I connect with my database, fill in a dataset and stream out the data to string, code:

        String SelectCmdString = "select * from dbo.Parts";
        SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter(SelectCmdString, "<your connectionstring">);
        DataSet myDataSet = new DataSet();
        mySqlDataAdapter.Fill(myDataSet, "BodyParts");

        foreach (DataRow row in myDataSet.Tables["BodyParts"].Rows)
        {
            if (row["imgPart"].GetType() != typeof(DBNull))
            {
                SqlBinary binData = new SqlBinary((byte[])row["imgPart"]);
                MemoryStream stm = new MemoryStream(binData.Value);
                Stream aStream = getMsgStrm(stm);
                StreamReader aReader = new StreamReader(aStream);

                string aMessage = aReader.ReadToEnd();

                //filter msg
                //write msg
            }
        }

I then write each string to an appropriate "txt" or "xml" depending on what u want, you can also filter out certain messages with regular expression, etc.

Hope this helps anyone, it sure as hell helped me.

Greetings