Hi All,
Recently on a project I had an integration where the destination wanted BizTalk to drop the message in a SQL Service Broker Queue.
The first thing I thought when I heard it was "SQL Queue?".Heard of MSMQ but SQL Queue,when did Microsoft come up with this concept.I really never heard of it and why we use it.
So I started googling on SQL Service Broker and found this good MSDN section
http://msdn.microsoft.com/en-us/library/bb522893.aspx
Here you will also find some tutorials to start working on
http://msdn.microsoft.com/en-us/library/bb839489.aspx
So after gaining some SQL Borker knowledge I googled as how this works with BizTalk.
For a scenario where
BizTalk needs to read a message from the SSB Queue this article from WCF Lob Adapter pack Team was a perfect sample.
http://blogs.msdn.com/adapters/archive/2008/06/30/using-the-wcf-sql-adapter-to-read-messages-from-ssb-queues-and-submit-them-to-biztalk.aspx
So now I know how BizTalk how BizTalk can read a Message from a SSB Queue.
But what about Sending a message to the SSB Queue?
So lets start with a simple scenario for this.
1.I will have to Enable SQL Service Broker on any database.Lets use the Master database for our scenario.
2.Note we dont need to create any table to work with SSB Queues and BizTalk.So you can Create a new Database and enable SSB on it.
2.Then we need to create a new Queue.
3.Run this Script in the SQL Query window and you should be ready with the basic setup.
USE master;
GO
ALTER DATABASE master SET NEW_BROKER with rollback immediate;
ALTER DATABASE master SET ENABLE_BROKER with rollback immediate;
GO
USE <your db name here>;
GO
CREATE MESSAGE TYPE
[Message1]
VALIDATION = None;
CREATE CONTRACT [Contract1]
([Message1]
SENT BY ANY
);
CREATE QUEUE Queue1;
CREATE SERVICE
Service1
ON QUEUE Queue1(Contract1);
So I created only 1 Queue named "Queue1" and with a contract and message type.
Dont worry this is not Biztalk MessageType.
Now we will create a SQL Stored Procedure which will insert a xml in this queue.
USE [master]
GO
/****** Object: StoredProcedure [dbo].[InsertMsgInQueue] Script Date: 10/01/2009 11:47:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
Create PROCEDURE [dbo].[InsertMsgInQueue]
-- Add the parameters for the stored procedure here
@XMLIn XML
AS
BEGIN
DECLARE @RequestMsg XML;
SELECT @RequestMsg = @XMLIn;
DECLARE @DlgHandle UNIQUEIDENTIFIER; BEGIN DIALOG @DlgHandle
FROM SERVICE [Service1] TO SERVICE N'Service1'
ON CONTRACT [Contract1] WITH ENCRYPTION = OFF
SEND ON CONVERSATION @DlgHandle MESSAGE TYPE [Message1] (@RequestMsg);
END
Now I will have to call this generic SP from BizTalk Send port and insert any XML in the SSB Queue(Queue1).
So now its time for BizTalk part of this Scenario.
Scenario :When we drop a file in a Folder the Receive port picks up the file and sends the file to the Send Port via a Filter(ReceivePortName).
I hope every BizTalk Developer knows how this works....ping me if you dont.
No Orchestration is involved.
Next we need to configure the Send port so we can send the message to the Queue.
Just configure the Send port as Shown below in Screen1 and Screen2.
Screen1
Screen2
The Template code inside Screen2 is
<InsertMsgInQueue xmlns="http://schemas.microsoft.com/Sql/2008/05/Procedures/dbo">
<XMLIn>
<bts-msg-body xmlns="http://www.microsoft.com/schemas/bts2007" encoding="string"/>
</XMLIn>
</InsertMsgInQueue>
Thats it.
Now start the ports drop any xml file in the "In" Folder and it should be insert inside the Queue.
No Orchestration,Only pure Message Based Routing simple solution.
Hope it helps somebody as I found it tough when I googled for a sample on this scenario.
So this was it all about Sql Service Broker and BizTalk.
Happy BizTalking.
