Assigning MSBuild Properties to SQLCMD Variables - Home of the Data Dude - Site Home

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:

Add the variable to the Database.sqlcmdvars file
Add a XML fragment to the database project (.dbproj) file
Use the SQLCMD variable

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.

Right click on the database project node inside the Visual Studio Solution Explorer, this will show the following menu, where you choose the “Unload Project” option
Now the project is unloaded and the Solution Explorer windows only shows the project node as “(unavailable)”.
Now right click on the project node again and choose the “Edit <project name>.dbproj” options
Go to the end of the project file and add the following ItemGroup before the end of the </Project> element
```
   1:    <ItemGroup>
```
```
   2:      <SqlCommandVariableOverride Include="ProjectDirectory=$(MSBuildProjectDirectory)" />
```
```
   3:    </ItemGroup>
```
The ItemGroup defines an element of type SqlCommandVariableOverride and contains an attribute which is a string that has on the left hand side of the equation the SQLCMD variable name and on the right hand side the MSBuild property name. Now you have mapped the MSBuild property to the SQLCMD variable
Right click on the database node inside Solution Explorer again, now choose the “Reload Project” option. This will close the project file inside the XML editor and open the project again. If you did not close the editor, Visual Studio will tell you this action will close the project file inside the editor.
Now we can use the variable, and to test we first add the following statement to the Script.PreDeplomenent.sql file.
```
   1:  print N'SQLCMD ProjectDirectory = $(ProjectDirectory)'
```
To test it we need to deploy the project, to the database, not just to script, otherwise the pre-deployment script will not get executed. The result should like something like this inside the Output window: