Michael Stephenson

MsBuild Task for Scripting Custom Mapping Tables

Introduction

On many BizTalk projects you have to deal with managing mapping of reference data. A number of times in the past I've spoken about the features in BizTalk for mapping reference data however they do have some limitations which mean that it is very common for a project to implement their own tables in a custom database to hold mapping reference data.

While this is entirely valid you often end up with the pain of how do you manage the reference data mappings. One of the simplest ways is to just have a script with all of the statements in to create the data. This has the benefit that it can be source controlled and versioned etc, however when you get a large amount of mappings it can become a real pain, especially if you have many columns involved in the mappings.

On my current project we had exactly that problem and I decided that I would trade off the script being in source control to have a master copy of the data held in a controlled database and then during by build process I would extract the data to produce a nice tidy script which could be used to create the mappings during the deployment. My rough requirements that I wanted to implement were as follows:

• Define the tables I want to extract to a script
• Allow the master data tables to have additional columns to allow notes and stuff to be associated with a record so the extract would define which columns to extract
• The extract will plug into an MsBuild process
• I can flow a version number from the build to be written to the script
• I will clean the tables before populating the reference data

     

All fairly straightforward stuff but I thought it might be useful to others doing BizTalk or .net projects.

The MsBuild Task

In the build task I use a class generated from an xml schema shown below.

This schema defines the input definition of which tables to extract and what columns. The below pic shows an example input definition.

Some information on the attributes:

Once the task is given its parameters and definition it will iterate through the tables and produce the script to refresh the data in your database when you do a deployment activity. We produce the script during the build process and then it gets deployed with our versioned build output.

The below sample from the tests in the sample shows how to use the task, and you will need to remember to do the normal MsBuild task activities such as referencing the task etc.

The Produced Script

As you can see below the script produced by the build task has taken the data for the tables from the definition file and pulled it all together into a script which you can easily deploy.

As you can see a fairly easy task to make life a little easier for us and hopefully it will make things a little less hassle for other teams too.

Alternatives

There are tools which do database diff's such as SQL Compare and Visual Studio Database Edition, but you may not have them and in the case here I'm not actually doing a diff as the tables would not necessarily match in terms of schema because I may want additional columns to manage the data.

Source Code

The source code is available from the following link:

http://www.box.net/shared/xdsc2lgmzv

Note: Some places block box so if you have problems getting the code drop me an email

• Share This Post:
• 
• 
• 
•   Short Url: http://wblo.gs/agj

Print | posted on Thursday, July 22, 2010 7:18 PM | Filed Under [ BizTalk ]