BizTalk Cross Reference Data Management Strategy
- by charlie.mott
Article Source: http://geekswithblogs.net/charliemott
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:
    http://home.comcast.net/~sdwoodgate/xrefseed.zip
    http://geekswithblogs.net/michaelstephenson/archive/2006/12/24/101995.aspx
    http://geekswithblogs.net/charliemott/archive/2009/04/20/value-vs.id-cross-referencing-in-biztalk.aspx
Options
Current options to managing this data include:
    Maintaining xml files in the format that can be used by the out-of-the-box BTSXRefImport.exe utility.
    Use of user interfaces that have been developed to manage this data:
    
        BizTalk Cross Referencing Tool
        XRef XML Creation Tool
    
    
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 settings.
    The 'BizTalk Cross Referencing tool' generates integers in the common id field. I prefer to use a string (e.g. acme.country.uk). 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 clients or BizTalk target instances.  If you upload the data for one client it would destroy the data for another client.  Yet in TFS where builds run concurrently, this would 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 data.
Naming Conventions
All data keys use namespace prefixing.  The pattern will be <companyName>.<data Type>.  The naming conventions will be to use lower casing for all items.  The data must follow this pattern to isolate it from other company cross-reference data.  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.erp
            acme.portal
            acme.assetmanagement
            
        
        
            xref_AppInstance.appInstance
            Application Instances 
            (each will have a corresponding application type).
            acme.dynamics.ax
            acme.dynamics.crm
            acme.sharepoint
            acme.maximo
        
        
            xref_IDXRef.idXRef
            Holds the cross reference data types.
            acme.taxcode
            acme.country
        
        
            xref_IDXRefData.CommonID
            Holds each cross reference type value used by the canonical schemas.
            acme.vatcode.exmpt
            acme.vatcode.std
            acme.country.usa
            acme.country.uk
        
        
            xref_IDXRefData.AppID
            This holds the value for each application instance and each xref type.
            GBP
            USD
        
    
SQL Scripts
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.purgexref.sql
            SQL script to clear acme.* data from the xref tables.  As such, this will not impact data for any other company.
        
        
            acme.applicationInstances.sql  
            SQL script to insert application type and application instance data.
             
        
        
            acme.vatcode.sql
            acme.country.sql
            etc ... 
            There will be a separate SQL script to insert each cross-reference data type and application specific values for these types.