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

  • Article

This post is a follow-up to https://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="https://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo">

<RequestMsg>

<bts-msg-body xmlns="https://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