This post is a follow-up to 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 and explains how to push a message using the WCF SQL Adapter from BizTalk to a SQL Service Broker (SSB) queue.
Scenario
1. An XML message is dropped to a file share
2. This XML message is made available to the WCF SQL Adapter by using the File Adapter
3. The WCF SQL Adapter then pushes this XML message to a preconfigured SSB queue by invoking a Stored Procedure
Create the database artifacts required for the SSB conversation
1. A message type, which denotes the format of the message in the queue
2. A contract, which denotes the conversation between a sender and a receiver and also includes the type of message flowing between them
3. The Initiator & Target queues, where messages are stored
4. The Initiator & Target services, which utilize the above queues
USE master;
GO
ALTER DATABASE <your db name here>
SET ENABLE_BROKER;
GO
USE <your db name here>;
GO
CREATE MESSAGE TYPE
[//SqlAdapterSSBSample/RequestMessage]
VALIDATION = WELL_FORMED_XML;
CREATE CONTRACT [//SqlAdapterSSBSample/SampleContract]
([//SqlAdapterSSBSample/RequestMessage]
SENT BY INITIATOR
);
CREATE QUEUE InitiatorQueue1DB;
CREATE SERVICE
[//SqlAdapterSSBSample/InitiatorService]
ON QUEUE InitiatorQueue1DB;
CREATE QUEUE TargetQueue1DB;
CREATE SERVICE
[//SqlAdapterSSBSample/TargetService]
ON QUEUE TargetQueue1DB
([//SqlAdapterSSBSample/SampleContract]);
5. A stored procedure, say InitiatorSP, that will take the message as an argument and push it to the SSB queue. Let’s use the name RequestMsg for the argument.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[InitiatorSP]
@RequestMsg xml
AS
BEGIN
DECLARE @DlgHandle UNIQUEIDENTIFIER;
BEGIN DIALOG @DlgHandle
FROM SERVICE
[//SqlAdapterSSBSample/InitiatorService]
TO SERVICE
N’//SqlAdapterSSBSample/TargetService’
ON CONTRACT
[//SqlAdapterSSBSample/SampleContract]
WITH ENCRYPTION = OFF;
SEND ON CONVERSATION @DlgHandle
MESSAGE TYPE
[//SqlAdapterSSBSample/RequestMessage]
(@RequestMsg);
END
GO
Create the BizTalk artifacts
1. Start the BizTalk Server 2009 Administration Console
2. Create a new BizTalk application, say SSBSendApplication
3. Create a new Receive Port, say FileReceivePort and add a new Receive Location, say FileReceive
a. Set the Type to File and configure the Receive Folder to point to a local share, say c:\in
4. Create a new Static One-way Send Port, say SqlSendPort
a. In the General tab,
i. Set the Type to WCF-SQL
ii. Click Configure and set the properties as follows
1. In the General tab, set
a. Address – the format is “mssql://<servername>/<instancename>/<databasename>”. For example, on my machine (using the default instance of SQL server), mssql://localhost//SSBTestDb (where SSBTestDb is the name of my database)
b. Action – the format is “TypedProcedure/<schemaname>/<storedprocedurename>”. For example, in my case, it is TypedProcedure/dbo/InitiatorSP
2. In the Messages tab, select Template and fill in the XML box with the following
<InitiatorSP xmlns=”http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo”>
<RequestMsg>
<bts-msg-body xmlns=”http://www.microsoft.com/schemas/bts2007″ encoding=”string”/>
</RequestMsg>
</InitiatorSP>
*Note that this approach requires that the xml encoding is string.
iii. Leave the other properties as is
b. In the Filters tab, add a filter BTS.ReceivePortName == FileReceivePort
5. Create a new Static One-way Send Port, say FileSendPort
a. In the General tab, set the Type to File and configure the Receive Folder to point to a local share, say c:\out
b. In the Filters tab, add a filter BTS.SPName == SqlSendPort
6. At this point the configuration of BizTalk application is completed, so start the application.
Send the message to SSB queue
1. Drop a request file to the c:\in share (one that file receive port is using). Note that this exact message will show up in the SSB queue. Here’s a sample message
<RequestMessage>Hello World</RequestMessage>
Consume the message from SSB queue
1. You can now consume the message from the SSB queue. On running the below query, you will see the above message.
DECLARE @DlgHandle UNIQUEIDENTIFIER;
DECLARE @RecvMsg XML;
RECEIVE TOP (1)
@DlgHandle=conversation_handle,
@RecvMsg = CAST(message_body as XML)
FROM TargetQueue1DB;
IF NOT (@DlgHandle IS NULL)
BEGIN
END CONVERSATION @DlgHandle;
SELECT @RecvMsg AS ReceivedMessage;
END