Geeks With Blogs
Paul's Petrov Whiteboard [BizTalk, Enterprise Application Integration, Business Process Automation, SOA, .NET]

This example is not covered in the ESB Toolkit samples and I bumped my forehead a few times while making it work. I thought it’d be helpful to save other fellow BizTalk'ers from headaches by publishing findings.

I have been fun of dynamic generic messaging for quite a while and went long ways to avoid working with typed messages and static bindings when it made sense. Nowadays, with the ESB Toolkit one does not have to spend much effort to achieve this goal. But one has to learn intricacies of configuration to unleash unlimited flexibility of the Toolkit.

Let's say we have a requirement to store some messages on service bus to the database. And the number of message types supported is growing plus their schemas can potentially change. If the database is SQL Server we can use an ESB Off-Ramp dynamically bound to WCF-SQL send port.

The send port interface can be implemented different ways. One is to generate typed WCF-SQL Adapter schemas for database objects (tables or procedures). Then create maps to transform original message to the adapter schema. This may potentially lead to complex maps that perform poorly, difficult to maintain and support. We also have to deploy new schemas every time new type is added.

Otherwise, we could use one-way port calling stored procedure that accepts entire message as a parameter and internally maps/saves message to the database. This saves us from creating and deploying stored procedure schema and map, as well as schema for the response message. We also gain performance on the BizTalk end by offloading XML to relational model mapping to the database engine which it does more efficiently. I chose this approach over the first.

Suppose, we have two message types http://contoso.com/schemas#PurchaseOrder and http://contoso.com/schemas#Invoice. We have created two stored procedures InsertPurchaseOrder and InsertInvoice. Each stored procedure accepts one parameter, the message body:

CREATE PROCEDURE InsertPurchaseOrder 
    @messageBody nvarchar(max)
AS
BEGIN
    SET NOCOUNT ON;
    
    DECLARE @docHandle int
    EXEC sp_xml_preparedocument @docHandle OUTPUT, @messageBody, N'<ns0:PurchaseOrder xmlns:ns0="http://contoso.com/schemas" />'

-- Perform mapping and insert here using OPENXML for example

END

Create BRE policy to resolve endpoint configuration:

 

In the Action area there are a lot of things being set up. Let's go through them one by one:

Parameter

Value

Notes

Set Endpoint Message Exchange Pattern

One-Way

Establishes exchange pattern, we don’t need solicit-response in our case

Set End Point Outbound Transport Type

WCF-Custom

This is the binding we are going to use.

Note: this value is not available in the ESB.TransportTypes vocabulary. Thus, we use literal string value “WCF-Custom”

Set Endpoint Outbound Transport Location

mssql://sqlservername//databasename?

This is standard form of the WCF-SQL uri. The example given is for default SQL instance (notice double slash)

Set End Point WCF Action

{Procedure/dbo/InsertPurchaseOrder}

This is from WCF-SQL adapter schema, but the trick here, it wants to be enclosed in {} which is not obvious

Set End Point Target Namespace

http://schemas.microsoft.com/Sql/2008/05

 

Set End Point Config

See below

That’s where “magic” is configured and deserves separate consideration

Endpoint configuration details

BindingType=sqlBinding&OutboundXmlTemplate=<InsertPurchaseOrder xmlns="http://schemas.microsoft.com/Sql/2008/05/Procedures/dbo"><messageBody><bts-msg-body xmlns="http://www.microsoft.com/schemas/bts2007" encoding="string"/></messageBody></InsertPurchaseOrder>&OutboundBodyLocation=UseTemplate&BindingConfiguration=<binding name="sqlBinding" />

Parameter

Value

Notes

BindingType

sqlBinding

 

BindingConfiguration

<binding name="sqlBinding" />

Default binding configuration which can be augmented.

OutboundXmlTemplate

<InsertPurchaseOrder xmlns= http://schemas.microsoft.com/Sql/2008/05/Procedures/dbo>

<messageBody>

<bts-msg-body xmlns="http://www.microsoft.com/schemas/bts2007" encoding="string"/>

</messageBody>

</InsertPurchaseOrder>

This is key parameter to make adapter accept the message and pass to stored procedure. The root element is the procedure name. The immediate child element is the procedure’s parameter. The enclosed element is to tell where to place message body.

OutboundBodyLocation

UseTemplate

Tells adapter to use template rather than raw message body.

This is another parameter which was not apparent since there’s no enumeration to provide possible values. Digging into adapter reference helps.

Add Messaging service to the Itinerary with the BRE endpoint resolver that uses policy created earlier. Then simply use one-way off-ramp. In the dynamic one-way port configuration PassThroughTransmit pipeline will do the job.

Once all these components implemented you have truly generic infrastructure to save any message to the database with minimal overhead. As new messages added, you simply create new stored procedure and rule that associate message type with it. And there are no BizTalk artifacts to deploy.

Posted on Wednesday, September 28, 2011 12:13 PM BizTalk , EAI | Back to top


Comments on this post: ESB Off-Ramp with Dynamic Resolver to WCF-SQL Binding

# re: ESB Off-Ramp with Dynamic Resolver to WCF-SQL Binding
Requesting Gravatar...
Hi Paul.. Just be cautious with the Dynamic SQL send port. Its going to kill your perfomance on high load volumes, it obviously cannot be scaled. If the volume is low you might get away with it . Just a quick note there.. Cheers - Zeeshan
Left by Zeeshan on Sep 28, 2011 6:16 PM

# re: ESB Off-Ramp with Dynamic Resolver to WCF-SQL Binding
Requesting Gravatar...
Hi Zeeshan,

Yes, I'm aware of the overhead of dynamically building WCF channel stack. As always, it's a trade off between some factors, in this case it's flexibility vs. performance. Thanks for the note.

Best regards,
Paul
Left by Paul on Oct 26, 2011 8:02 AM

# re: ESB Off-Ramp with Dynamic Resolver to WCF-SQL Binding
Requesting Gravatar...
Hi Paul, i am trying to implement the above steps, but got an error during execution -
Microsoft.ServiceModel.Channels.Common.XmlReaderParsingException: The start element with name "bts-msg-body" and namespace "http://microsoft.com/schemas/bts2007" was unexpected. Please ensure that your input XML conforms to the schema for the operation.

Could you please let me know what i may be doing wrong? Thanks.
Left by Jose on Mar 24, 2014 10:57 PM

Your comment:
 (will show your gravatar)


Copyright © Paul Petrov | Powered by: GeeksWithBlogs.net