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:

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

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

Some extra info:

• I use a fantastic tool called XMLPad to discover/test XPath expressions, read more at XMLPad – a new tool in my developer utility belt
• MSBuild.ExtensionPack.Xml.XmlFile is a msbuild task used to edit XML files and is available from Mike Fourie’s MSBuild Extension Pack
• I’m using a property called $(BuildNumber) to hold the value to substitute into the file and also $(DESTINATION)\**\$(CONFIGURATION)\**\*.publish.xml to define an ItemGroup all of my Publish Profile files. Populating those properties is basic msbuild stuff and is therefore outside the scope of this blog post however if you want to learn more check out MSBuild properties & How To: Use Wildcards to Build All Files in a Directory.

Hope this is useful!

@Jamiet