THE SQL Server Blog Spot on the Web

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

Jamie Thomson

This is the blog of Jamie Thomson, a freelance data mangler in London

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) :

image

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:

  <ItemGroup>
    <Namespaces Include="myns">
      <Prefix>myns</Prefix>
      <Uri>http://schemas.microsoft.com/developer/msbuild/2003</Uri>
    </Namespaces>
  </ItemGroup>
 
  <Target Name="UpdateBuildNumber">
    <ItemGroup>
      <SSDTPublishFiles Include="$(DESTINATION)\**\$(CONFIGURATION)\**\*.publish.xml" />
    </ItemGroup>
    <MSBuild.ExtensionPack.Xml.XmlFile Condition="%(SSDTPublishFiles.Identity) != ''"
                                       TaskAction="UpdateElement"
                                       File="%(SSDTPublishFiles.Identity)"
                                       Namespaces="@(Namespaces)" 
                                       XPath="//myns:SqlCmdVariable[@Include='DeployTag']/myns:Value" 
                                       InnerText="$(BuildNumber)"/>
  </Target>

The important bits here are the definition of the namespace http://schemas.microsoft.com/developer/msbuild/2003:

image

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

image

Some extra info:

Hope this is useful!

@Jamiet

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

Comments

 

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

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement