I just started using the ESB Toolkit, and I wanted to create an itinerary that sends messages to the SQL Adapter to do an insert action.

I found the sample on configuring the SQL WCF port with some help with of the ESB Toolkit forum, so no problems there. But the documentation for the sample is non-existent. So I thought I should give you the head-start I did not get. It’s really not that difficult.

First things first, I am assuming you already know how to do a two-way two-way itinerary. Without mapping this would be a request response itinerary with four sequential shapes, the fourth connecting back to the first:

  1. On ramp (two way)
  2. Routing Service Messaging Extender (itinerary service, select messaging as extender, Routing as service)
  3. Off ramp extender (itinerary service)
  4. Off ramp (two way)

You add a resolver to the messaging extender to enter the parameters for the dynamic send port.

WCF SQL Adapter through an Itinerary

The ‘magic’ for calling the SQL adapter is all in that resolver. To keep things easy, we use a static resolver with the following properties

  • Resolver implementation: static
  • Transport name: WCF-Custom
  • Transport location: mssql://myserver//mydatabase?
  • Endpoint configuration: BindingType=sqlBinding
  • Action: {TableOp/Insert/dbo/myTable}

Basically you can copy those attributes from the binding file that was generated when you used the generate schema option for the WCF SQL Adapter. I did not test if it is possible to use the BTSActionMapping construct but it would make more sense to decide the operation in a business rule resolver when you are doing ESB Toolkit anyway. In any case don’t forget the curly brackets around the action.

The actional sample, the SqlAdapter-TwoWay-MessageRouting-MessageTransform-MessageTwoWaySendPort.itinerary file contains an additional mapping, but is basically the same.

Don’t forget, before you install this sample you have to install the dependent samples: Dynamic Resolution Sample, Resolver Service Sample and the Itinerary On ramp sample.

This sample you can try out yourself by executing setup.bin in the installscripts folder, then execute Esb.Itinerary.Test.exe from the itinerary sample. Select Two Way Service in web service options and deselect Use WCF Service,  Load the itinerary xml provided in the itineraries folder of the sample, load up the request message from the testdata folder and press request. If all is well, you should get the response back.