ESB Off-Ramp with Dynamic Resolver to WCF-SQL Binding

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 way 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.

Other way with minimal BizTalk coding is to 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. Simply put, stored procedures perform better and simpler to maintain. 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.