Handling duplicate messages in BizTalk

Hi,

   I would suggest the following solution:

1) Create a table in SQL database with two columns: a field to store your project name, and another to store a datetime value.

2) Create a stored procedure in the same database, passing two parameters: one parameter of type string that represents a project name, the other of type datetime. In your SP, code logic to first do a look up on the table (defined in step 1) to see if you already have such a row stored in the table for given project name and datetime passed to the SP: if yes, then return appropriate value; if no, then insert the record and return appropriate value.

3) In a Visual Studio Biztalk project, use WCF-SQL adapter to create a schema for your Stored procedure in Generated Items wizard. You can promote both fields (as parameters to your SP) in the schema as Distinguished fields.

4) In another Biztalk project, create your orchestration artifact, reference the SP schema assembly created in step 3 and set up a message type for it, then in an Expression shape, parse the received filename from your Receive Port using context properties to store the project name and datetime stamp (from your filename) into variables defined at your orchestration level; in a Message Assignment shape, assign both variables to the Distinguished fields of your SP schema, and create a logical Request/Response Send port to pass this message to your SP; use a Decide shape to check the response message, and depending on the content of that message, either do further processing (in that branch) if it’s the message received that day, or do nothing (in that branch) if it’s a duplicate message.

   Hope this helps,

   Daniel.