I am receiving the following question a lot: How do I propagate MSBuild properties to SQLCMD variables. In order to make the Visual Studio 2008 Team System Database Edition GDR release use the data from an MSBuild property inside a SQLCMD variable you need to do three things:
First you need to add a SQLCMD variable to the Database.sqlcmdvars file, in this example we will add the $(ProjectDirectory) variable, which we want to use the reflect the MSBuild $(MSBuildProjectDirectory) property. Set the value to something that you can recognize as not being set, you cannot leave it empty, so I will use “notset”.
Now that we have set the variable, we need to add some information to the database project file to map the value from the MSBuild property to the SQLCMD variable we just defined.
In order to achieve this we need to add an ItemGroup to the project file. There is no UI support for doing this, so you manually need to edit the database project file. There are multiple ways of doing this, I will describe one, which assumes you have the project loaded inside Visual Studio as a starting point.
2: <SqlCommandVariableOverride Include="ProjectDirectory=$(MSBuildProjectDirectory)" />
1: print N'SQLCMD ProjectDirectory = $(ProjectDirectory)'
Now that we know the variable assignment works, we can use the variable in other more interesting places.
In the next post I will cover how to make the variable assignment conditional.
GertD @ www.DBProj.com