0

Using Visual Studio 2013 with TFS and SQL Server 2012 & 2014, and SQLPackage.exe with a Publish profile.

Does anyone know if/how to get the Build version or Assembly version into a SQLCMD variable?

i.e. I'm trying to obtain whatever system-level build versioning information TFS/Visual Studio has about an SQL project build and push this into a SQLCMD variable and then insert that into a table.

I know that I can explicitly define an SQLCMD variable and then manually set and increment the value of it, but I want whatever build version number has for the project.

I've been able to get the Assembly version info via MSBuild, as detailed here: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/e0c93a55-d8bd-4a32-89d9-f46013fc1235/automatic-version-increment-on-datatier-applications?forum=ssdt

But that is outside of my post-deploy.sql script. I've tried simply referencing the $(IntermediateTargetAssembly) variable, but the project won't build and errors that this variable not been declared.

Simon Wray
  • 192
  • 4
  • 12
  • This article (http://sqlblog.com/blogs/jamie_thomson/archive/2010/10/19/maintaining-version-history-in-your-database-using-visual-studio-2010.aspx) is very close to what I'm looking to do, specifically the details around `/p:BuildVersion="$(BuildLabel)"` but it applies to VS2010 and msbuild, rather than sqlpackage. – Simon Wray Feb 16 '16 at 15:16
  • The way I did this was to put my properties into a publish profile, then update the publish profile at build time. As the publish profile is xml its just a case of replacing the inner text using an xpath query – David Martin Feb 17 '16 at 13:34
  • @david-martin Currently I've got some a hard-coded `$BuildVersion` variable in the Publish profile and Post-Deploy script... are you saying that you are able to update the Publish profile xml with a dynamically generated value from a TFS/VS2013 property? or are you updating it with a value you are entering manually or lifting from a table? Thanks! – Simon Wray Feb 18 '16 at 13:09
  • No sorry I don't do that, I have a property and update the publish profile prior to deployment via msbuild, think search and replace. Its not the best solution, but works for me, hence the comment rather than answer. – David Martin Feb 18 '16 at 13:35

1 Answers1

0

I realize this is a different deployment setup than you are currently using but I think you will find it may work out better overall.

http://dotnetcatch.com/2016/02/10/deploying-a-database-project-with-msdeploy/

You could set the build version on your MSDeploy package using the MSBuild property you found via MSDeploy Parameterization to update a SQL script that would insert or update the source version in your database.

chief7
  • 14,263
  • 14
  • 47
  • 80