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

Answer 2

Extract Messages from suspended instances

Scenario:

BizTalk 2010 and SQL 2008 R2 is the environment we have used fore this scenario.

You have problem with some integrations, 1500 suspended instances inside BizTalk and you need to send the actual messages to a customer, and then you properly do not want to manually save out this from BizTalk Administrator.

There are a lot of blogs and Internet resources pointing out vbs, powershell scripts how to do this, but I have used BizTalk Terminator to solve this kind of scenarios.

As you now BizTalk terminator is asking you 3 questions when the tool starts

I.1.All BizTalk databases are backed up? II.2.All Host Instances is stopped? III.3.All BizTalk SQL Agents is stopped? This is ok when you are going to actually change something inside BizTalk databases but this is not what you are going to do in this scenario you are only using the tool to read from BizTalk databases. But you should always have backups off BizTalk databases.

You are always responsible for what you are doing, but when we have used this tools in the way I describe we have not have any problem with this scenario.

So after you have start Terminator tool please click yes to the 3 questions(you dont need to stop anything in this scenario) then connect to the correct environment please do this in your test environment first so you feel comfortable with this scenario, the next step is to choose a terminator task choose Count Instances(and save messages) after this you have to fill in the parameter TAB with correct serviceClass and Hostname and set SaveMessages to True and last set FilesaveFullPath to the correct folder you want to save the messages to.

Then you can choose to click on the Execute Button and depending of the size and how many it can take some time, after this disconnect Terminator do NOT do anything else.

You should now if you have filled in the correct values in the parameter TAB have the saved messages inside the FilesaveFullPath folder.

Download BizTalk terminator from this address:

http://www.microsoft.com/en-us/download/details.aspx?id=2846