THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Jamie Thomson

This is the blog of Jamie Thomson, a data mangler in London working for Dunnhumby

Editing sqlcmdvariable nodes in SSDT Publish Profile files using msbuild

Publish profile files are a new feature of SSDT database projects that enable you to package up all environment-specific properties into a single file for use at publish time; I have written about them before at Publish Profile Files in SQL Server Data Tools (SSDT) and if it wasn’t obvious from that blog post, I’m a big fan!

As I have used Publish Profile files more and more I have realised that there may be times when you need to edit those Publish profile files during your build process, you may think of such an operation as a kind of pre-processor step. In my case I have a sqlcmd variable called DeployTag, it holds a value representing the current build number that later gets inserted into a table using a Post-Deployment script (that’s a technique that I wrote about in Implementing SQL Server solutions using Visual Studio 2010 Database Projects – a compendium of project experiences – search for “Putting a build number into the DB”).

Here are the contents of my Publish Profile file (simplified for demo purposes) :


Notice that DeployTag defaults to “UNKNOWN”.

On my current project we are using msbuild scripts to control what gets built and what I want to do is take the build number from our build engine and edit the Publish profile files accordingly. Here is the pertinent portion of the the msbuild script I came up with to do that:

    <Namespaces Include="myns">
  <Target Name="UpdateBuildNumber">
      <SSDTPublishFiles Include="$(DESTINATION)\**\$(CONFIGURATION)\**\*.publish.xml" />
    <MSBuild.ExtensionPack.Xml.XmlFile Condition="%(SSDTPublishFiles.Identity) != ''"

The important bits here are the definition of the namespace


and the XPath expression //myns:SqlCmdVariable[@Include='DeployTag']/myns:Value:


Some extra info:

Hope this is useful!


Published Monday, December 10, 2012 2:42 PM by jamiet

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



RKong said:

A suggestion from my coworker led me to simply use a FielUpdate. I use RegEx on the publish.xml files and do a simple find and replace.

March 26, 2013 11:33 AM

Leave a Comment


This Blog


Privacy Statement