[ Link to the sample: http://code.msdn.microsoft.com/Using-EAI-Bridges-with-SQL-be2b5761]

SQL Azure lookup is a very useful feature available with Service Bus EAI Bridges. Using this feature, a bridge can go ‘lookup’ a SQL Azure database table, pick a value, and pass it into the message at runtime, while the message is still being processed.  For example, assume a message received by the bridge from an insurance organization includes a ClaimType element. But the intended message receiver wants ClaimType as well as ClaimDescription elements in the message.  Also assume that the ClaimType to ClaimDescription mapping is stored in a SQL Azure database table. In that case, this is how the process would flow:

  1. The bridge will extract the value in the ClaimType element in the incoming message.
  2. For the extracted ClaimType value, the bridge will use the SQL Azure lookup feature to look up the corresponding ClaimDescription value.
  3. The bridge picks the ClaimDescription value and puts it in the message that exits the bridge.

So, as you see, the message that exits the bridge has both ClaimType and ClaimDescription, as required by the message receiver. However, the message from the original sender only had ClaimType. This is called ‘Data Enrichment’. For more information on this feature, see the “Enrich Stage” section at the link http://msdn.microsoft.com/en-us/library/windowsazure/hh689793.

I have uploaded a sample on the MSDN code gallery that demonstrates SQL Azure lookup using a business scenario. I’ll be publishing a tutorial on the business scenario as well, pretty soon. However, the sample is already available here: http://code.msdn.microsoft.com/Using-EAI-Bridges-with-SQL-be2b5761.

Do try it out and please send in your feedback, comments, questions, etc