June 17, 2014 
You’ve versioned your assemblies using Team Build before…
Pre-Build script path
I’m sure you are all aware of the nice little PowerShell script “ApplyVersionToAssemblies.ps1” that updates your C# AssemblyInfo.cs files, you can find it on codeplex here. It is part of a great project named Community TFS Build Extensions which provides a place for build engineers to share workflow activities, build process template files, PowerShell scripts and tools for Team Foundation Build.
But what about a SQL Server Project? If we take a look at the project properties we can see the following
Data-tier application properties
If any of those values are changed from their defaults, the following elements are automatically added to your .sqlproj
- DacVersion
- DacApplicationName
- DacDescription
.sqlproj
Unfortunately the PowerShell script above does nothing for us. Luckily we can easily tweak that same PowerShell script to inject our version information into the .sqlproj project file. This particular method may not be suitable for you but you could also inject the version information into a post-deployment script if you wanted to insert the version number into a SQL table….
We’ll continue with the DacVersion approach which allows us to leverage the functionality found in a data-tier application.
What is a Data-tier Application? Here is the definition from MSDN:
A data-tier application (DAC) is a logical database management entity that defines all of the SQL Server objects – like tables, views, and instance objects, including logins – associated with a user’s database. A DAC is a self-contained unit of SQL Server database deployment that enables data-tier developers and database administrators to package SQL Server objects into a portable artifact called a DAC package, also known as a DACPAC.
I am going to tweak the ApplyVersionToAssemblies.ps1 by adding the following lines to the bottom of the script. Note: I am referencing a function named Set-XmlElementsTextValue which I added to the beginning of the script. The function is only a few lines which does the following:
$node = Get-XmlNode -XmlDocument $XmlDocument -NodePath $ElementPath
# If the node exists, update its value.
if ($node)
{
$node.InnerText = $TextValue
}
Set-XmlElementsTextValue
The Build Number Format defined in the Build Process Template is the following: $(BuildDefinitionName) 1.0.$(Date:yy)$(DayOfYear)$(Rev:.rr)
When I execute my build definition which also triggers Release Management (in this example). You’ll notice the version number is 1.0.14164.01
SQL-RM 1.0.14164.01
In Release Management I have a component named DACPAC Database Deployer v12 which calls sqlpackage.exe with the .dacpac from the drop folder, I’ve added the parameter RegisterDataTierApplication=True to the components command-line options. I can easily check my database instance to see the version of the data-tier application that is deployed by executing the following query:
SELECT * FROM [msdb].[dbo].[sysdac_instances]
This is the result I get back when I run it against my database instance, you’ll see that the version matches exactly what was created by Team Build and my SQL-RM Build Definition.
data-tier applications
If you have any questions or comments, please leave them below. This is one method you could use to identify the version of your SQL database in your release pipeline.
Azure DevOps and Visual Studio Awesomeness 
Wes…
Thanks for the post. Looks very useful. One question for you.
In your method Set-XmlElementsTextValue method you call Get-XmlNode. which I assume is another method you wrote. I have been trying to use SelectSingleNode in it’s place with little luck. What does your Get-XmlNode look like?
Dave
LikeLike
Hi Dave,
Here is the function I’m using in my PowerShell script:
function Get-XmlNode([ xml ]$XmlDocument, [string]$NodePath, [string]$NamespaceURI = “”, [string]$NodeSeparatorCharacter = ‘.’)
{
# If a Namespace URI was not given, use the Xml document’s default namespace.
if ([string]::IsNullOrEmpty($NamespaceURI))
{
$NamespaceURI = $XmlDocument.DocumentElement.NamespaceURI
}
# In order for SelectSingleNode() to actually work, we need to use the fully qualified node path along with an Xml Namespace Manager, so set them up.
$xmlNsManager = New-Object System.Xml.XmlNamespaceManager($XmlDocument.NameTable)
$xmlNsManager.AddNamespace(“ns”, $NamespaceURI)
$fullyQualifiedNodePath = “/ns:$($NodePath.Replace($($NodeSeparatorCharacter), ‘/ns:’))”
# Try and get the node, then return it. Returns $null if the node was not found.
$node = $XmlDocument.SelectSingleNode($fullyQualifiedNodePath, $xmlNsManager)
return $node
}
LikeLike
Is there an Azure DevOps version of this task?
LikeLike
No but the PowerShell script used above is located here: https://github.com/wesmacdonald/blog-code-snippets/tree/master/build/PowerShell
Wes
LikeLike