December 2011 - Posts

Azure Service Bus EAI/EDI December 2011 CTP – Content Based Routing
18 December 11 03:11 PM | wmmihaa

In this blog post we are going look at how to manage routing in the new Azure ServiceBus EAI CTP.

As a scenario, I’m going to send a request for information (RFI) to some of my fellow MVP’s. To do that, I’m going to create a One-Way Xml Bridge, to receive the messages. After receiving the RFI message I tend to route it to one of three queues.

1. Create a ServiceBus project

If you haven’t already downloaded the SDK, you can do this here. After you’ve installed the SDK, you can sign in to the labs environment using a Windows Live ID.

Open Visual Studio 2010, and select Create Project. In the list of project templates, select ServiceBus, and Enterprise Application Integration. Give it a name and click Ok.


2. Create a Message Type

Right-click the project and select Add->New Item. At this time there are two types of artifacts you can add; Schemas and Maps. Select Schema and sett an appropriate name. In my case I set the name to RFI.xsd. Continue building up your schema. Notice, you don’t have to promote any nodes as you’d have to do in BizTalk.


3. Designing the Bridge

Double-click the BridgeConfiguration.bcs and drag a Xml One-Way Bridge from the toolbox to the canvas. This is going to be your entry point to your process, similar to a Receive Location in BizTalk. Set the name appropriately, and notice the Router Address which is going to be your endpoint in Azure ServiceBus.


4. Add Queues

As stated before, the incoming RFI message is going to be routed to any of the three queues. You might not your message relayed to a queue, and could there for use any of the other Destinations such as Relay- or External Service EndPoints. Either way, the principle of routing is the same.

Connect the Bridge with all Destinations.


5. Configure the Bridge

Next we’ll define the incoming message type(s). Double-click your Bridge (ReceiveRFI in my case). Click the plus button in the Message Types stage. Select the Message Type you created earlier, and click the arrow button on the right.


6. Enrich the Message

This is the interesting step, where we are going to promote some fields in the payload so that we can route on these in the next step.

First open your schema and select the node you care to use for routing (in my case Receive). Copy the Instance XPath from the Property window.


Then double-click the Bridge, select either of the Enrich stages, and then click the Property Definition button in the Property window. There are two Enrich stages, as you might be using a Transformation, in which case you might want to promote fields from either the original message or the transformed message.

For more information about transformations, have a look at Kent’s post.


Set the Type to XPath, and paste the XPath expression in the Identifier text box. Select the Message Type and set the name of the property. Finish be setting the data type and click the Add button (+). Close the dialog by clicking the Ok button.


7. Set the routing conditions

As you have promoted your property (or properties), you’re now ready to set the Filter Conditions on each of the Connectors. Select one of the selectors and type the Filter in the Property window. Eg. receiver=’SteefJan’ or customerId=1234.


8. Create the Queues

Before we deploy the solution, you need to create the queues. There are several tools for this, but with the samples comes a MessageReceiver project you can use.

Type MessageReceiver.exe <Your namespace> owner <Your issuer key> <Queue name> Create 

After creating the queues verify they are created in the portal.


9. Deploy your solution

Right-click the project and select Deploy. Supply the secret.


10. Test the solution

Along with the MessageReceiver tool, you’ll find a MessageSender project as well. Just type:

MessageSender.exe <Your namespace> <Your issuer key> <Your endpoint> <Path to sample file> application/xml

Use the MessgeReceiver to get the messages from the queues:



Filed under: , ,
Using User-defined tables as stored procedure parameter
15 December 11 01:47 PM | wmmihaa

The WCF-SQL adapter provides support for multiple inserts through the Consume Adapter Service feature:


However, sometimes you might want to validate the data on the SQL side before before making the insert. For instance, if you have a collection of Customers, where some of them might already exist in the database, and should only be updated. In such a case, you’d have to first make a database lookup, to determine the state of the Customer and then make either an insert or update.

In such a case, using user-defined table types might be your solution. User-defined tables are similar to ordinary tables, but can be passed in as a parameter.

In my sample, I have a Contacts table, and I’m receiving a collection of Persons where some entities are new and some are to be updated.


Create the User-Defined Table Type

The user-defied table type will serve as our contract.

CREATE TYPE [dbo].[InsertContactRequest] AS TABLE
    [PersonNo] [varchar](50) NOT NULL,
    [FirstName] [varchar](50) NOT NULL,
    [LastName] [varchar](50) NOT NULL,
    [Phone] [varchar](50) NOT NULL,

Create the Stored Procedure

The stored procedure takes the user-defined table type as a parameter (@insertContactRequest), then updates all existing rows and inserting all new once.

CREATE PROCEDURE [dbo].[sp_InsertContacts] @insertContactRequest InsertContactRequest READONLY
    UPDATE dbo.Contacts 
    SET Phone = r.Phone
    FROM dbo.Contacts c
    JOIN @insertContactRequest r on r.PersonNo = c.PersonNo

    INSERT INTO dbo.Contacts (PersonNo, FirstName, LastName, Phone)
    SELECT r.PersonNo, r.FirstName, r.LastName, r.Phone
    FROM    @insertContactRequest r
    WHERE    r.PersonNo not in(SELECT PersonNo FROM dbo.Contacts)

Generate BizTalk artefacs

1. In you Visual Studio, right-click the BizTalk project and select Add->Add Generated Items. Select Consume Adapter Service.


2. In the Consume Adapter Service dialog, click the configure button to set the credentials. Click Ok, and then Connect.


3. In the tree-view, select Strongly Typed Procedures, and select your stored procedure in the right pane. Click Add and Ok to generate the schemas.


4. Make your transformation, and complete your solution.



Here is the sample source.


(Kudos Daniel Östberg)

Filed under: , ,
I did it so you don't have to: Connecting to Dynamics CRM Online from BizTalk Server
11 December 11 02:31 PM | wmmihaa

If you’re a consultant like me, you’ve probably got similar calls from some key account manager, as I did yesterday:

KAM: Hi Mikael. I’m just about to close this super big deal, with this super important Customer.
Me: Really! Good for you.
KAM: Yeah, we’re really close. But to rap it up, I was wondering if you could help me out a bit…
Me: Sure. What do you have in mind?
KAM: Could you come with me to meeting with the customer on Monday? (this happens on Thursday 6PM)
Me (getting suspicious): mmm…What do you want me to do?
KAM: A demo!
Me: Demo of what?
KAM: The customer want us to show how to integrate Dynamics CRM Online with SAP using BizTalk.
Me: What?
KAM: Yes, yes. The customer wants us to show it live! You know, they want to see you do it…
Me: ARE YOU HIGH? (I didn’t actually say that, but I was thinking it)
Me: It will not happen! I haven't worked with SAP since 3-4 year ago. I’ve never worked with CRM Online (or off-line for that matter). I’m fully booked tomorrow, and I want to spend the weekend with my family as X-mas is coming up.
KAM: But we need to close this deal…
Me: NO!
KAM: Please…
Me: No way!
(Yada, yada, yada)
Me: Ok, I’ll give it a try (I’M SUCH AN IDIOT!!!!!)

So here it is: How to connect to Dynamics CRM Online from BizTalk Server

To begin with, if you want to integrate with CRM Online, you have two options. Either use an un-typed web-service API or use a tool called CrmSvcUtil.exe to create a proxy class for you. Each of these comes with some challenges and limitations:

Using an un-typed web service, can of course be somewhat messy, but the SDK provides you with the schemas you need (more on that later). The biggest challenge, however, is to authenticate to the service as it assumes you’re using Windows Live Id. Authenticating against the service would require an additional four calls to the service to finally get the authentication tokens needed to create the security header. An then figure out a way to to add the headers in a pipeline. The steps needed are described by Girish Raja here.

The proxy created using the CrmSvcUtil is quite nice, since it’s typed, but of course I can’t use it in a send port. I would  have to make the call using the inline-send approach from within an expression shape in an orchestration. And thereby loose the built in re-send functionality and more, that ships with BizTalk.

As none of these approaches was acceptable, I begun looking for other alternatives. What I really wanted was an authentication behavior, that I could add to my WCF-Custom send port adapter. 

Building the Custom WCF Behavior

What I needed was a Message Inspector that would build up the security header as Girish Raja did in his sample, and then add that header to the SOAP envelope. This class is called LiveIdAuthenticationMessageInspector and inherits from IClientMessageInspector. This gives two methods to my class: BeforeSendRequest and AfterReceiveReply.

public object BeforeSendRequest(ref System.ServiceModel.Channels.Message request, 
    System.ServiceModel.IClientChannel channel)
    string securityHeader = HeaderHelper.GetSecurityHeader(this._username, this._password, this._crmUri);
        new SecurityHeaderSerializer(securityHeader),true));

    return null;

In the BeforeSendRequest method is where I can add the security header to the message before the message is sent out. The the BeforeSendRequest method I call a helper class returning the actual header. The GetSecurityHeader method is going through four steps to build up the header:

  1. Get Windows Live Device Credentials
  2. Register Device Credentials and get binaryDAToken
  3. Get Security Token by sending WLID username, password and device binaryDAToken
  4. Build up the security header with the token from previous step.

(This sample does not cache the tokens! I strongly suggest you add some caching logic before you run this in production)

After the header is created it is added to the request, using a custom serializer, as it would otherwise be HTML encoded.

public void AfterReceiveReply(ref System.ServiceModel.Channels.Message reply, object correlationState)
    Trace.WriteLine("[bLogical] LiveIdAuthenticationMessageInspector:AfterReceiveReply called");
    int index = reply.Headers.FindHeader("Security", WSSecurityUsernameTokenProfileNamespace);

When BizTalk (or any other WCF client) receives the response it will throw an exception, as it doesn’t understand the Security header. I might have gone away with adding the http: schema to BizTalk, but as I don’t need it, I just removed it from the header on the way back.

Part from the Message Inspector, I also added an EndpointBehavior and a BehaviorExtensionElement. The LiveIdAuthenticationBehaviorExtensionElement needs to be registered in the configuration using the behavior in BizTalk

After you have registered the behavior, you can focus on the normal BizTalk tasks like building orchestrations and mappings. To get started with consuming the CRM Services, have a look at Richards post. The only thing I’d like to emphasis is that the correct schemas are part of the SDK (sdk\schemas). After you run the Consume WCF Service Wizard, remove all schemas and replace them with the the once in the SDK. Better yet, put all those schemas in a separate schema project, and reference that project from other projects where you’re using them.

1. Add the behavior to the Global Assembly Cache

Open up the LiveIdAuthentication project, build and add it to the global assembly cache.

2. Register the behavior in the configuration

(sorry about the formatting).

    <add name="liveIdAuthentication" type="LiveIdAuthentication.LiveIdAuthenticationBehaviorExtensionElement, LiveIdAuthentication, Version=, Culture=neutral, PublicKeyToken=698ceec8cebc73ae"/>

You can do this either in a config file (machine.config or BTSNTSvc[64].exe.config) or in BizTalk (WCF-Custom Send handler):


I prefer the later as I would otherwise need to make the changes to all the servers in the group. Just copy the extension element above, into a config file, and import the file from the Transport Properties dialog above. Or you can point to the app.config file in the sample.

3. Add the Endpoint Behavior

Open the send port and click the Configure button to open the WCF Transport Porperties. Select the Behavior tab and right-click the Endpoint behavior node, and select Add extension. Select the liveAuthentication extensions. Select the extension and set the properties.



You’re done.

Use this code as you like, and on your own risk. If you make improvements, I’d appreciate if you notify me. One thing I know could be done better, would be to cache the tokens and re-use them for the next call.


Download the Dynamics CRM LiveId Authentication Behavior sample here.


This Blog


    MVP - Microsoft Most Valuable Professional BizTalk User Group Sweden BizTalk blogdoc

    Follow me on Twitter Meet me at TechEd


    Locations of visitors to this page


    The opinions expressed herein are my own personal opinions and do not represent my employer's view in anyway.