BizTalk Server: Detecting a Missing Message or Orchestration, V2

  • Article

History

Two years ago the original author wrote about how to detect a missing message in a BizTalk app.  This is a useful pattern when we expect something to happen, such as in incoming file, on a regular basis.  That article can be found here: BizTalk Server: Detecting a Missing Message

That original solution involved a simple Convoy Orchestration that would reset itself after an interval.  If that interval expired, an alert message would be sent for administrative or other action.  Although this is a perfectly serviceable solution, since then, the original author has changed his preferred implementation to address this requirement.

This article describes how to achieve the same result by querying the Tracking database.

The Visual Studio Solution can be found in the MSDN Code Gallery here: BizTalk Server: Detecting a Missing Message or Orchestration, V2

Sample Implementation

The primary implementation of this new pattern is a Stored Procedure that queries the Tracking Database, BizTalkDTADb, for the last started instance of a custom Pipeline.  If the last instance is started outside the configured interval, a message is returned that can be acted on in BizTalk.

CREATE PROCEDURE  [dbo].[Checkservice] 
  -- Add the parameters for the stored procedure here 
  @ServiceName        VARCHAR(50), 
  @MaxIntervalMinutes INT
AS
  BEGIN
      SET nocount ON; 
 
      DECLARE @currentTime AS DATETIME 
 
      SET @currentTime = Getutcdate() --All Tracking data is in UTC 
      SELECT LastSI.strservicename AS [ServiceName], 
             LastSI.dtstarttime    AS  [LastStartTimeUTC] 
      FROM   (SELECT TOP  1 s.strservicename, 
                           si.dtstarttime 
              FROM   [BizTalkDTADb].[dbo].[dta_serviceinstances] AS si WITH (nolock) 
                     JOIN [BizTalkDTADb].[dbo].[dta_services] AS s WITH (nolock) 
                       ON si.uidserviceid = s.uidserviceid 
              WHERE  s.strservicename = @ServiceName 
              ORDER  BY  si.dtstarttime DESC) AS  LastSI 
      WHERE  Datediff(minute, LastSI.dtstarttime, @currentTime) > 
             @MaxIntervalMinutes 
  END

The Stored Procedure is called from a Polling Receive Location using a command such as:

EXEC Checkservice 'WhenNothingHappens.SomeMessageReceived', 60

In this example, “WhenNothingHappens.SomeMessageReceived” is the custom Pipeline Type used on the Receive Location where we are expecting to receive a file at least every 60 minutes.

Notable Points and Configuration Settings

Custom Pipeline – This technique requires a custom Pipeline for the Receive operation, even if it’s exact same composition of a build-in Pipeline such as XmlReceive.  This is to provide a unique name to query in the Tracking Database.  In this case, SomeMessageReceived consists of just the XmlDisassembler component.

Tracking Settings – This technique works with the default Tracking settings.   The required settings are “Enable group-level tracking” checked on the Group and “Port start and stop events” checked on Pipeline.

Separate Database – The Code Gallery Solution includes scripts for a separate Database to house the CheckService Stored Procedure.  This is because it is not supported in any way to modify a BizTalk database.  However, a dedicated database is not required and this Stored Procedure can be added to any app owned database.

Security – By default, regular BizTalk Host Instance Accounts and Groups do not have read access to Tracking Database tables.   The Tracking Host works through Execute access to a specific set of Stored Procedures.  To allow the Host Instance running this Receive Location to read the Tracking Database, you should:

  • Create a dedicated Service Account, Host and Host Instance.
  • Add the Service Account to the BizTalk Server Operators Group.  BizTalk Server Operators automatically has Read access to the Tracking Database.

Stored Procedure Output – If the last Pipeline Start Time is within the interval, the Stored Procedure returns no records so no message is created.  This is considered the success case.  If the last Instance is outside the interval, the Pipeline name and last Start Time, in UTC, is returned.  This message can be handled in BizTalk like any other message.

Further Considerations

This is a basic example of using Tracking to solve a common problem.  Some easy extensions for a custom implementation are:

  • A check on Service Status to consider only successful operations.
  • Customizing the Receive Location window to achieve a once per day scenario.
  • Adding a day of week filter.
  • A custom process for handling the generated alerts.

Happy Tracking!

See Also

Another important place to find a huge amount of BizTalk related articles is the TechNet Wiki itself. The best entry point is BizTalk Server Resources on the TechNet Wiki.