Continuing on with using PowerShell to help with BizTalk administration, I'd like to focus on suspended messages.
Sometimes, one of our internal departments expects a message to come in at a particular time. There are times, however, that the message was never sent to BizTalk for processing. Either way, the "I don't see an expected message, can you see if it failed?" routine is communicated our way. Keep in mind that we already have put mechanisms in place to notify if something failed... :)
At any rate, I've developed a quick PowerShell script to see if anything indeed has suspended. This one uses SQL, and it hooks into the BizTalk MessageBox Database; make sure permissions are set accordingly. This post is rather code agnostic, but the approach is to use PowerShell to be consistent with the other scripts which our group has put in place.
The query is a common, read-only script that you may have seen elsewhere:
The next step is to do the traditional SQL routine, create the SQL connection, open it, put it into a SQL adapter, and so on.
Now you have a list of all suspended messages in a typical .NET DataSet. From there we can slice and dice the information. I like sorting and grouping everything out, and PowerShell does a fantastic job of that.
The result is a list of unique BizTalk applications in your environment with a suspended message count for each application. If there aren't any suspended messages, obviously you won't have any rows in your DataSet, and you can notify your customers that everything is operating as expected in BizTalk. ;)
