Monday 27 August 2012

Biztalk Suspended Messages Email Notification


In Biztalk it is usual to get the message suspended due to some run time issues or due to some other reasons.
               
But we need to be notified when the message is suspended in the console with the error description (cause for the suspension).

We can achieve this by enabling the Routing Failure reports in the Console and consume the message by the Send Port (SMTP) and we can notify by a  mail.

But when the Routing Failure report is enabled the suspended message is not available in the console that is we can’t able to resume the suspended messages later, because the message is consumed.
               
To Achieve the notification and also the Messages need to be suspended in the console, we can achieve By Custom Pipelines , tools like Biztalk360 (http://www.biztalk360.com/) and by querying the database.

By using the stored Procedure (Querying the DB) , we can query and get the suspended messages and the error reason and send a notification mail (Database Mail ) in a HTML format as shown below. 

Sample Mail with the List of Suspended Messages with the appropriate Application Name, Adapter Name, URI,Reason


below links helps in configuring  the Database Mail 

Scheduled Task to Execute the Stored Procedure :

To get Notified you can schedule the Task and as per the scheduled time, the suspended messages will be consolidated in a table and send a mail as shown above.

Bat File to Run the Stored Procedure which needed to schedule the Task :

You can create a bat file with the below code by replacing the appropriate values

    sqlcmd -Q "EXEC  [databaseName].[dbo].[storedProc Name] " 
                  -S SystemName 
                  -d DatabaseName
                  -E -o      C:\log.txt