Friday, April 22, 2011

BizTalk 2010 SQL Adapter

I'm finally working with BizTalk 2010, after hearing about it for quite a while. I love the new mapper.

I was using the new WCF-SQL Adapter. I like the new way of doing things, it seems much easier than to use than the old one. In order to invoke it, I right clicked the project and then choose Add / Add Generated Items. Then I clicked Consumer Adapter Service and clicked Add.

On the new dialog box, under Select a binding I chose sqlBinding, and then clicked Configure. On the popup, I chose Windows security. On the URI Properties tab, I entered values for Server and InitialCatalog, and then clicked OK. Back to the Consume Adapter Service dialog, where I clicked Connect, which refreshed some metadata below. Using the tree under Select a Category, I navigated to Strongly-Typed Procedures. I then clicked on my stored proc under Available categories and operations and clicked Add. I entered a filename prefix for the new schemas, and clicked OK.

Two new schemas were created, along with a binding file for the WCF port. The schema file that I use for creating messages, mapping, etc. ends with ...TypedProcedure.dbo.xsd. There's another schema that appears to create a schema that has the types for records to be returned from the proc. One is for stored procs returning one record, and another for stored procs returning multiple records. By default, the ...TypedProcedure.dbo.xsd uses the type for single records, but it looks as though it would be pretty easy to set it up for multiple records.

So I created my messages and imported the binding file WcfSendPort_SqlAdapterBinding_Custom.bindinginfo.xml using BizTalk Admin Console. When I first tried sending a message through the port, I was surprised to see the following message:

The adapter failed to transmit message going to send port "WcfSendPort_SqlAdapterBinding_TypedProcedures_dbo_Custom" with URL "mssql://localhost//StevesTest?". It will be retransmitted after the retry interval specified for this Send Port. Details:"Microsoft.ServiceModel.Channels.Common.UnsupportedOperationException: The action "<BtsActionMapping xmlns:xsi="" xmlns:xsd="">
<Operation Name="GetByAccountNo" Action="TypedProcedure/dbo/GetByAccountNo" />
</BtsActionMapping>" was not understood.

Server stack trace:
at System.Runtime.AsyncResult.End[TAsyncResult](IAsyncResult result)
at System.ServiceModel.Channels.ServiceChannel.SendAsyncResult.End(SendAsyncResult result)
at System.ServiceModel.Channels.ServiceChannel.EndCall(String action, Object[] outs, IAsyncResult result)
at System.ServiceModel.Channels.ServiceChannel.EndRequest(IAsyncResult result)

Exception rethrown at [0]:
at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)
at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)
at System.ServiceModel.Channels.IRequestChannel.EndRequest(IAsyncResult result)
at Microsoft.BizTalk.Adapter.Wcf.Runtime.WcfClient`2.RequestCallback(IAsyncResult result)".

I don't remember where I found the answer to this, but it turns out that in the send port, in the SOAP action header, you have to specify the Operation Name as the same value that the Operation is inside of the Orchestration send port. By default in the Orchestration, it is Operation_1. By default when the binding for the port was created, it was GetByAccountNo, the name of the stored proc. When I made the name of the Operation on the port inside the orchestration the same as the Operation Name in the binding, all was well.

This is actually the first time I can remember that the name of the Operation inside of an Orchestration in BizTalk had significance. Until now I have always left the Operation name as the default.