Using the WCF SQL Adapter to submit messages to SSB queues from BizTalk


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

Comments (1)