Geeks With Blogs
Charlie Mott

Article Source:

This article describes an approach to the management of cross reference data for BizTalk.  Some articles about the BizTalk Cross Referencing features can be found here:


Current options to managing this data include:

However, there are the following issues with the above options:

  • The 'BizTalk Cross Referencing Tool' requires a separate database to manage.  The 'XRef XML Creation' tool has no means of persisting the data.
  • The 'BizTalk Cross Referencing tool' generates integers in the common id field. I prefer to use a string (e.g. This is more readable. (see naming conventions below).
  • Both UI tools continue to use BTSXRefImport.exe.  This utility replaces all xref data. This can be a problem in continuous integration environments that support multiple target BizTalk groups (even different clients).  If you upload the data for one client/group/application it would destroy the data for every other application in that group.  Yet in TFS, where builds run concurrently, this could break unit tests.

Alternative Approach

In response to these issues, I instead use simple SQL scripts to directly populate the BizTalkMgmtDb xref tables combined with a data namepacing strategy to isolate client\application data.

Naming Conventions

All data keys use namespace prefixing.  The pattern will be <company name>.<biztalk group and\or applicatoin>.<data type>.  The data must follow this pattern to isolate it from other company\group cross-reference data.  The naming convention I use is lower casing for all items. 

The table below shows some sample data.  (Note: this data uses the 'ID' cross-reference tables.  The same principles apply for the 'value' cross-referencing tables).

Table.Field Description Sample Data
xref_AppType.appType Application Types acme.petshop.erp
xref_AppInstance.appInstance Application Instances
(each will have a corresponding application type).
xref_IDXRef.idXRef Holds the cross reference data types. acme.petshop.vatcode
xref_IDXRefData.CommonID Holds each cross reference type value used by the canonical schemas. acme.petshop.vatcode.exmpt
xref_IDXRefData.AppID This holds the value for each application instance and each xref type. UK


The data to be stored in the BizTalkMgmtDb xref tables will be managed by SQL scripts stored in a database project in the visual studio solution.

File(s) Description
Build.cmd A sqlcmd script to deploy data by running the SQL scripts below. (This can be run as part of the MSBuild process).
acme.petshop.purgexref.sql SQL script to clear acme.petshop.* data from the xref tables.  As such, this will not impact data for any other client/group.
acme.petshop.applicationInstances.sql   SQL script to insert application type and application instance data.
etc ... 
There will be a separate SQL script to insert each cross-reference data type and application specific values for these types.
Posted on Sunday, March 14, 2010 12:32 PM | Back to top

Comments on this post: BizTalk Cross Reference Data Management Strategy

No comments posted yet.
Your comment:
 (will show your gravatar)

Copyright © charlie.mott | Powered by: | Join free