Lookups

Another usage of message properties is with transformation. Here, a common requirement is transcoding, where one value needs to be replaced with or mapped to another value. Code tables can be used for this purpose, and we can use the lookup capabilities of BizTalk Services to do this and then feed the values into a transform to replace the value in the message.

Some preparation is needed to set things up. If you recall, when you provision a new Windows Azure BizTalk Services instance, you can choose to create a new SQL Azure database to hold the various tables needed by the service. We can also create a table in this database to hold the transcoding data for the lookups. To create a table and add data to it, run the following script against the database:

CREATE TABLE [dbo].[CountryMap](
  [CountryName] [nvarchar](100),
  [ISOCountry] [int] NULL
  CONSTRAINT [PK_CountryName] PRIMARY KEY CLUSTERED
  (
    [CountryName] ASC
  ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY...