Geeks With Blogs

News View Michael Stephenson's profile on BizTalk Blog Doc View Michael Stephenson's profile on LinkedIn
Michael Stephenson keeping your feet on premise while your heads in the cloud

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:

   

Attribute

Information

ScriptDatabaseToUse

Is the name of the destination database which will be injected into the out put script as a use statement [Optional]

IncludeIdentityInsert

Indicates if the Identity Insert feature should be enabled before each tables data is added

MasterDataTableName

The name of the table in the master reference data database to extract

ScriptDataTableName

The name of the table in the destination database to add the records to

SourceColumnName

The name of column in the master reference data database table to extract

ScriptColumnName

The name of the column to insert that value to in the data

DataType

Supports String, Number, DateTime and is used to format the value added to the script from the data extracted. Feel free to add additional if you use need to

   

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

Posted on Thursday, July 22, 2010 7:18 PM BizTalk | Back to top


Comments on this post: MsBuild Task for Scripting Custom Mapping Tables

# re: MsBuild Task for Scripting Custom Mapping Tables
Requesting Gravatar...

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 activityjuly 2017 Calendar Printable

july 2017 Calendar Template
Left by sudhasharma on Jun 03, 2017 3:02 AM

# re: MsBuild Task for Scripting Custom Mapping Tables
Requesting Gravatar...
Advance bo mens,oking is there only at the tourist information.Who decides rather spontaneously, receives the
clothing,
Left by linming0303 on Nov 08, 2017 6:43 PM

Your comment:
 (will show your gravatar)


Copyright © Michael Stephenson | Powered by: GeeksWithBlogs.net