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