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:
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:
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. acme.petshop.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 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.
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.
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).
(each will have a corresponding application type).
||Holds the cross reference data types.
||Holds each cross reference type value used by the canonical schemas.
||This holds the value for each application instance and each xref type.
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.
||A sqlcmd script to deploy data by running the SQL scripts below. (This can be run as part of the MSBuild process).
||SQL script to clear acme.petshop.* data from the xref tables. As such, this will not impact data for any other client/group.
||SQL script to insert application type and application instance data.
|There will be a separate SQL script to insert each cross-reference data type and application specific values for these types.