Tracking Events In Real Time Using BizTalk 2006

Recently I was tasked with creating a solution within BizTalk 2006 to track real-time events. The scenario did not sound unique and with the RFID functionality built into BizTalk 2006 R2, the situation seems likely to become more pervasive. Here is a stripped down sample of the solution that I arrived at Sample Timer Application

The Problem:

In this sample scenario we have the following set-up:

• We have a warehouse in Death Valley, CA where we store pallets of ice cream. Some areas of the warehouse are refrigerated, some are not. The areas that are not refrigerated have an average temperature of over 100 degrees Fahrenheit. Ice Cream has a life expectancy of 20 minutes when left outside of a refrigerated area.
• Pallets are equipped with RFID tags that identify the pallet by a unique Pallet ID.
• Gateways between refrigerated areas and un-refrigerated areas are equipped with RFID readers. When an ice cream pallet passes through a gateway a message is sent to BizTalk indicating a change in status between refrigerated/exposed.
• The external loading dock door is also equipped with an RFID reader. Passing through this portal indicates that the pallet has left the warehouse.
• We need to be able to send an alert to the warehouse foreman to alert him when a pallet has been left exposed for 15 minutes so that he can take action before the pallet is ruined.

Based on this scenario the following requirements were compiled:

• When a message is received indicating that a pallet has changed status from refrigerated to exposed, a 15 minute timer should be started.
• If the pallet is returned to refrigerated status or leaves the warehouse on a truck the timer should be terminated to prevent false alerts from executing.
• If the timer expires without being terminated, an alert message should be generated by the system.

In order to solve this problem the following design was constructed:

Trigger messages are received from the Message Box (through direct binding) by the Timer Orchestration. Depending on the trigger type (Create, Reset, or Terminate) a corresponding stored procedure is called via a SQL send port. Trigger messages contain the following fields:

• Timestamp - Timestamp of the message. In our solution, this timestamp is used as a base time in the following formula. BaseTime + TimerLength = TimerEndTime. TimerEndTime is the timestamp contained in the database that indicates when a timer should expire.
• InterfaceName - Namespace of the trigger type. Used for routing.
• Action - The action we are trying to perform (CreateTimer, ResetTimer, or TerminateTimer).
• SubAction - Unique name for this timer.
• Params - Collection of Key-Value pairs for future processing. (Note: the Create and Reset Timer Triggers require a TimerLength key with an integer (timer length in minutes) in order to process. Any number of Param nodes can be added to this collection. (e.i. in our example brand, flavor, and expiration date could be included, to name a few).

SQL send ports are used to execute one of the three actions on the database:

• Create - Creates a record in the database. Uses spCreateTimer stored procedure.
• Reset - Updates a specified record in the database to give it a new TimerEndTime, if the timer has not expired. Uses spResetTimer stored procedure.
• Terminate - Updates a specified record in the database to flag it as expired. Uses spTerminateTimer stored procedure.

A SQL receive location is set up in BizTalk to poll the results of the spGetExpiredTimers stored procedure. This stored procedure aggregates timers from the database whose TimerEndTime timestamps are in the past. Timers that are flagged as expired are ignored. Timers collected are flagged as expired to prevent duplicate processing.