Configuring the WCF-SQL Adapter with a BizTalk ESB Toolkit 2.0 Messaging Itinerary Service

I have recently been playing around with the Microsoft BizTalk ESB Toolkit 2.0 and am quite impressed with all the new features and capabilities compared to the previous version.

Until recently, I have only been using BizTalk as an Enterprise Application Integration (EAI) platform proper, as opposed to an Enterprise Service Bus (ESB), so I’m still trying to make sense of all the new terminology and concepts for a start.

One of the most interesting feature of the ESB Toolkit is the concept of Resolvers that are used for determining at run-time where to route messages to and what transformation to apply at each stage of the itinerary.

One scenario that I’m looking at is the possibility of calling a stored procedure based upon some criteria that will be defined dynamically, so I set out to understand how the SqlAdapter sample worked.

Using the WCF-SQL Adapter

The WCF-SQL Adapter can be used to call arbitrary stored-procedure and even to perform simple SQL statements against arbitrary tables and views in a SQL Server database.

It works by first having you generate XSD schemas to match both the call (or the select statement) with parameters and the result in the form of an array of datasets.

You have the choice to generate untyped or strongly-typed schemas. I’m not sure one would choose to go untyped, because the resulting schemas contain a couple of <Any> tags that I don’t know how to wire in subsequent BizTalk maps.

Surely, I will want to generate strongly-typed schemas for my solutions.

Configuring a Messaging Itinerary Service

Once the schemas are hosted in a BizTalk project, compiled and deployed, you can make use of them in an itinerary by configuring a Messaging service, like so :

When trying to understand and extend the SqlAdapter sample for my needs, I could not initially make it work against strongly-typed schemas and spent of lot of time figuring out what went wrong.

The key bit to understand is the Action field, that represents the name of the stored procedure that will be invoked when the message runs through the associated Off-Ramp.

The syntax for this field comes from the Consume Adapter Service dialog box, and depends upon the selected items for which to generate XSD schemas. The actual syntax for an item appears in the Node ID column-header for the Available categories and operations list view.

So, for instance, the standard syntax for calling a strongly-typed stored procedure is:

Action: {TypedProcedure/<database schema>/<name of the stored procedure>}

Likewise, the syntax for running a SELECT statement against a particular table is:

Action: {TableOp/Select/<database schema>/<name of the table>}

Perfectly logical, isn’t it? Well, it probably shows more of my inexperience with the WCF Adapter than the ESB Toolkit, but I sure spent a lot of time before it actually hit me!

I think the ESB Toolkit is a very promising product and we are actually evaluating how to introduce it in one of our projects as part of our integration platform.

I hope this little tip will help you if, like me, struggle with the configuration of an itinerary service that used the WCF-SQL Adapter.

This entry was posted in BizTalk ESB Toolkit 2.0. Bookmark the permalink.