Composite Operations on BizTalk WCF- SQL Adapter - Calling Multiple Store Procedures

The new SQL Adapter in the WCF Adapter Pack 2.0 supports composite operations, that is performing multiple SQL operations in response to a single input message. The purpose of this blog post is to provide a walkthrough of how this works. I am using the public beta of BizTalk Server 2009, the public beta of the WCF Adapter Pack 2.0 and released version of SQL Server 2008. See below for an important caveat, as well as a link at the end of the post to my test solution.

To start, as with most things in BizTalk’s contract-first world, we need a schema. In order to do this, choose “add generated items” from a BizTalk project in Visual Studio. Then, choose “Consume Adapter Service”. If you don’t see that Visual Studio template, then you haven’t installed the Adapter Pack, as that’s where it comes from.

Next, select the sqlBinding specify a server, and press “Configure”. Set the client credential type to Windows (assume appropriate SQL login rights), and then on the URI tab, specified the server and database to use:

Af6ter doing this, press “Connect”, and the metadata will be populated.

For the purpose of this walkthrough, I have created 2 stored procedures: the first one inserts a record into a table, the second returns all rows in that table. Those are shown in the UI below.

Note that in the category we have “Procedures” and “Strongly-Typed Procedures”. The distinction is that “Procedures” will create un-typed schemas, whereas “Strongly-Typed Procedures” will generate schemas that you can work with inside BizTalk for mapping, promoting properties, etc.

The “Filename Prefix” will be used as a prefix for all the generated schemas.

After that was configured, I clicked OK and all the schemas were generated for me.

Next step is that you need to create a composite schema that will define the message you send to the adapter. I’m not quite sure why this one wasn’t generated for me, it’d be nice (hint hint), but it’s trivial to do.

How I did this for the walkthrough:

• create a new schema
• rename the root to SQLMsg (or whatever you like, this is unimportant)
• add a sibling record called SQLMsgResponse (this name does matter, it is the name of the request, with “Response” appended)
• add two child records under SQLMsg, and another two under SQLMsgResponse (names don’t matter, they’ll get renamed below)
• right-click the topmost “<schema>” node, and in the “Imports” property, add the “CompositeTypedProcedure.dbo.xsd” schema
• in the first child under SQLMsg, set the “Data Structure Type” property to InsertIntoDestination (this is a reference that you just imported above)
• in the second child under SQLMsg, set the “Data Structure Type” property to SelectAllDestination
• in the first child under SQLMsgResponse, set the “Data Structure Type” property to InsertIntoDestination (this is a reference that you just imported above)
• in the second child under SQLMsgResponse, set the “Data Structure Type” property to SelectAllDestinationResponse

Your schema should now look like this:

 

The, create an instance of the new composite schema to use as a test message, and populate the request. Here’s mine:

I then created a simple orchestration that would receive a request, call the adapter, and persist the response from the adapter. The request and response messages are of the type we just created in the composite schema:

Build and deploy the solution. After deploying it, note that there was a binding file generated along with the schemas, which is awesome, as this means you don’t need to manually create the send port. So, import the binding file which exists in you Visual Studio project.

HOWEVER… pretty big caveat here…. after importing the binding, you need to change the action mapping. If you use the default value, it will fail. You need to replace what is generated with the magic keyword “CompositeOperation”. This tells the adapter that it needs to call multiple operations, which it will resolve based on the schemas and namespaces. I believe the reason this works the way it does is that it allows you to import multiple operations in a single pass, and then use some subset of those operations in a composite operation, thereby enabling re-use of the generated schemas to potentially cover multiple different combinations of composite operations. Either way, watch out for this one. The error message tells you exactly what the problem is, however it won’t tell you about the keyword.

As an aside, and for the benefit of those who have not worked with this adapter yet, here are the binding configuration properties you have access to:

Then:

• create an inbound file drop location
• create an outbound file drop folder
• bind everything
• start the application
• drop your instance doc into the file drop location, triggering the orchestration

Lastly, here’s the output file:

In closing, I think this is an awesome new capability, and I am really liking the new SQL adapter. In case you haven’t heard, the old SQL adapter is being deprecated, so you really should be working with this one going forward.