BizTalk 2013 file receive location trigger on non-event

Question

I have a file receive location which is schedule to run at specific time of day. I need to trigger a alert or mail if receive location is unable to find any file at that location.

I know I can create custom components or I can use BizTalk 360 to do so. But I am looking for some out of box BizTalk feature.

Answer 1

BizTalk is not very good at triggering on non-events. Non-events are things that did not happen, but still represent a certain scenario.

What you could do is:

• Insert the filename of any file triggering the receive location in a custom SQL table.
• Once per day (scheduled task adapter or polling via stored procedure) you would trigger a query on the SQL table, which would only create a message in case no records were made that day.

Also think about cleanup: that approach will require you to delete any existing records.

Another options could be a scheduled task with a custom c# program which would create a file only if there were no input files, etc...

Answer 2

The sequential convoy solution should work, but I'd be concerned about a few things:

1. It might consume the good message when the other subscriber is down, which might cause you to miss what you'd normally consider a subscription failure
2. Long running orchestrations can be difficult to manage and maintain. It sounds like this one would be running all day/night.

I like Pieter's suggestion, but I'd expand on it a bit:

Create a table, something like this:

CREATE TABLE tFileEventNotify
(
    ReceiveLocationName VARCHAR(255) NOT NULL primary key,
    LastPickupDate DATETIME NOT NULL,
    NextExpectedDate DATETIME NOT NULL,
    NotificationSent bit null,
    CONSTRAINT CK_FileEventNotify_Dates CHECK(NextExpectedDate > LastPickupDate)
);

You could also create a procedure for this, which should be called every time you receive a file on that location (from a custom pipeline or an orchestration), something like

CREATE PROCEDURE usp_Mrg_FileEventNotify
(
    @rlocName varchar(255),
    @LastPickupDate DATETIME,
    @NextPickupDate DATETIME
)
AS
BEGIN
  IF EXISTS(SELECT 1 FROM tFileEventNotify WHERE ReceiveLocationName = @rlocName)
  BEGIN
    UPDATE tFileEventNotify SET LastPickupDate = @LastPickupDate, NextPickupDate = @NextPickupDate WHERE ReceiveLocationName = @rlocName;
  END
  ELSE
  BEGIN
    INSERT tFileEventNotify (ReceiveLocationName, LastPickupDate, NextPickupDate) VALUES (@rlocName, @LastPickupDate, @NextPickupDate);
  END
END

And then you could create a polling port that had the following Polling Data Available statement:

SELECT 1 FROM tFileEventNotify WHERE NextPickupDate < GETDATE() AND NotificationSent <> 1

And write up a procedure to produce a message from that table that you could then map to an email sent via SMTP port (or whatever other notification mechanism you want to use). You could even add columns to tFileEventNotify like EmailAddress or SubjectLine etc. You may want to add a field to the table to indicate whether a notification has already been sent or not, depending on how large you make the polling interval. If you want it sent every time you can ignore that part.