BizTalk -> More on Constructing Messages and Configuration Information for an Orchestration

I recently was reading Scott's entry on Constructing Messages from Scratch with Embedded Resources. This is a good method, so take a look a look at it HERE.
Below is a variant on Constructing Messages in an Orchestration. This also can be used as a method to read any type of configuration information into an Orchestration.
This method needs:

1) A Sql Server table to store the templates for the XML Messages and/or Configuration Information.
2) A BizTalk XML Schema.
3) A BizTalk Map.


A Sql Server Table
Below is a create statement for the table with three columns:

if exists (select * from dbo.sysobjects where id = object_id(N'[GenericCodes]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [GenericCodes]
GO

CREATE TABLE [GenericCodes] (
 [Code] [varchar] (50)  NOT NULL ,
 [Value] [varchar] (7000)  NOT NULL ,
 [Description] [varchar] (500) NULL ,
 CONSTRAINT [PK_GenericCodes] PRIMARY KEY  CLUSTERED
 (
  [Code]
 )  ON [PRIMARY]
) ON [PRIMARY]
GO

Some sample data for the table is as below:

a) The Code column is the Primary Key of the table that describes the contents of the row.
b) The Value column contains the actual template XML message or Configuration value.
c) The Description column contains a description/purpose of the template xml message or configuration value,
 
In the example sample data above, the first two rows:

BaseXML_401ExtraInfoRequest
BaseXML_401FlatFileTemplate

contain template xml for messages that need to be constructed from scratch in an orchestration, sample as below:

<ns0:ShippingInfo ShipToAddress="Some Address" ShipToCity="Some City" ShipToCountry="Some Country" xmlns:ns0="http://ConstructingXMLMessages.ShippingInfo" />

The remainder of the rows, contains configuration information for a process:

Request401_FIFOMIBSend -> How many Processor orchestrations can be running at one time (1 or many)
Request401_MaxNumberofFilesToBatch -> How many incoming messages to batch together using a looping sequential convoy
Request401_TestMode -> To indicate if the system is in test mode or production mode.
Request401_TimeoutForFileBatching -> The max time to wait for the next message to batch.
Response401_DB2Process401Results_CommandTimeout -> Passed to a ADO.Net Helper component to update a DB2 database.
Response401_DB2Process401Results_Retries -> Used to configure a Component Retry Pattern.
Response401_DB2Process401Results_RetryInterval -> Used to configure a Component Retry Pattern.
TwoDigitYearSupport -> To help translate two digit years to four digit years in a map.
 

A BizTalk XML Schema

Create a BizTalk Schema called XmlTemplateAndConfigInfo.xsd as below:

Each node (could be an element or attribute) is of Data Type string. Notice that each nodes name matches a Code column name from a row in the Configuration table.
(Note: For this method to work, It is important to match the names. This will be explained below)
Each node is promoted to a distinguished field.
 

A BizTalk Map


Create a BizTalk Map called Map_PopulateXMLTemplateAndConfigInfo.btm as below:

Notice that the source and destination schemas use the same schema (XmlTemplateAndConfigInfo.xsd) that was described above.

A DataBase Lookup Functoid and Value Extractor functoid are used to populate each node on the Destination Schema.

The Configuration information for a DataBase lookup functoid looks like below:

The first Input parameter for a DataBase lookup functoid is the lookup value. In this case it should be set to a value of a row from the Primary Key Code column in the configuration table GenericCodes. Instead of Hard Coding this value in for example (Request401_TestMode), a link from the source schema is used. When the link has been created, go to the properties window as below and for the Source Links property set the value to -> Copy name. Therefore instead of the value of the source node being mapped over, the name of the node will be mapped over. This is why it is important for the name of each node to match a rows Code value in the GenericCodes table.  

The second Input parameter for a DataBase Lookup functoid is the DataBase Connection string A UDL file was used to hold the connection string for the database as described HERE

A Value Extractor Functoid is then used to extract the XML Template or Configuration Value and map it the Destination node.


A Sample Orchestration to use the XML templates and Configuration Values

Sample Orchestration as below:

The following Messages and Variables are created in the Orchestration View for the Orchestration:


Messages:
msgEmptyXmlTemplateAndConfigInfo MessageType is set to the XmlTemplateAndConfigInfo.xsd schema discussed above.
msgPopulatedXmlTemplateAndConfigInfo MessageType is set to the XmlTemplateAndConfigInfo.xsd schema discussed above.
msgCreateFromXMLTemplate is a test message that is constructed from an XML Template.

Variables:
Both varXMLDomEmptyXmlTemplateAndConfigInfo and varXMLDomCreateFromXMLTemplate are variables of type -> System.Xml.XmlDocument
They are used to help in message construction.


1) In the First construct shape in the Orchestration -> Construct EmptyXmlTemplateConfigInfo
A Message Assignment shape is used to construct message -> msgEmptyXmlTemplateAndConfigInfo
The code in the Message Assignment is as below:

// First need an empty message to construct the Empty Config Info XML Message
// Note: The XML is just the Root Node Name and the target namespace of the schema XmlTemplateAndConfigInfo.xsd
// This is hardcoded, but should never change.
// This constructed message is needed in the next orchestration shape which uses a map to construct the msgPopulatedXmlTemplateAndConfigInfo 
// message.
varXMLDomEmptyXmlTemplateAndConfigInfo.LoadXml(@"<ns0:XmlTemplateAndConfigInfo xmlns:ns0=""http://Demo.XmlTemplateAndConfigInfo""/>");
msgEmptyXmlTemplateAndConfigInfo = varXMLDomEmptyXmlTemplateAndConfigInfo;


2) In the Second construct shape -> Construct PopulatedXmlTemplateConfigInfo
A Transform shape is used to construct message -> msgPopulatedXmlTemplateAndConfigInfo.

The map -> Map_PopulateXMLTemplateAndConfigInfo.btm is used in the transform shape.
Message msgEmptyXmlTemplateAndConfigInfo is used as the Transform Source Message
Message msgPopulatedXmlTemplateAndConfigInfo is used as the Transform Destination Message 

The map -> Map_PopulateXMLTemplateAndConfigInfo.btm will then be invoked to construct the
msgPopulatedXmlTemplateAndConfigInfo. The nodes in this message will be populated from the values in Sql
table GenericCodes.

3) In the third construct shape, this is an example of constructing a message from an XML template,
that originates from the Sql table GenericCodes.
A Message assignment shape is used to construct message -> msgCreateFromXMLTemplate


// Load the Template XML from the msgPopulatedXmlTemplateAndConfigInfo.BaseXML_401ExtraInfoRequest node.
// Because Distinguished fields are used, then the intellisense works as below
varXMLDomCreateFromXMLTemplate.LoadXml(msgPopulatedXmlTemplateAndConfigInfo.BaseXML_401ExtraInfoRequest);
msgCreateFromXMLTemplate = varXMLDomCreateFromXMLTemplate;


4) Below is an example of reading the msgPopulatedXmlTemplateAndConfigInfo for Configuration information.
The Decide shape (Decide If In Test Mode, Test Mode Rule) uses the following expression to determine if in test mode:

// Intellisense can be used because the Nodes in the XmlTemplateAndConfigInfo.xsd were promoted as
// Distinguished Fields.
msgPopulatedXmlTemplateAndConfigInfo.Request401_TestMode == "1"


Finally

The good things about this method are:

1) No Code. You do not have to write any .Net Helper code to help read in the XML templates and Configuration Values into an Orchestration.
2) This is a simple method that I originally used at a client where .NET skills were scarce. Therefore why burden them with .NET code to maintain/test etc. when they do not have the resources to do so.
3) As soon as the value in the GenericCode tables changes it will be automatically visible.
4) A tool such as Query Analyzer or the Sql Enterprise Manager can be used to change the XML Template and Configuration values in the database.
5) This is a central repository of configuration data that any BizTalk Server in the Group will point to.


The not so good things about this method are:

1) For each node to be populated in the Map_PopulateXMLTemplateAndConfigInfo.btm map, a separate round trip to the database must be done. But we are talking milliseconds.

2) Each orchestration that needs the configuration values, would have to add the necessary Construct shapes to invoke the Population map. But, it would be easy to create one orchestration to centralize this procedure and then let the other orchestrations call into this "Configuration Orchestration" using a Call Orchestration Shape. The "Configuration Orchestration" would then return an msgPopulatedXmlTemplateAndConfigInfo message as an out parameter.

Just a Note

I always try to do a bit of research on my Blog topics so I can point the reader to other techniques and different views. So below are some links to other blogs on BizTalk and Configuration. Depending on your needs, take your pick:

1) The BizTalk Configuration Dilemma
2) Using the Rules Engine for orchestration configuration values *BizTalk Sample*...
3).NET configuration files with Orchestration Hosts 
4) How to store configuration data in the Enterprise Single Sign-On Database (SSO)
5)  Using the BTSNTSvc.exe.config
6) Maybe BizTalk 2006, has some new features for configuration? Just
installed my copy and have not had the time to look yet.