How To Compile SQLProj Files Using Cmdline MSBuild... Errors Included!
I recently needed to build and deploy about 40 small database projects that were in 4 or 5 different database solutions. And I needed to do this several times a day, so compiling via Visual Studio would be a boring and tedious process. So to speed up the process I decided to write the build process in an MSBuild target file and call initiate the build process through PowerShell. The targets file was simple enough to put together.
This would be saved in the root location of all the solution folders as "BuildAllDBProjects.targets.xml". Then the PowerShell would be simple enough; just save the script to the same location so that we can make use of the $PSScriptRoot variable. Note also that I am using the 32bit MSBuild location (to use 64 bit alter "Framework" to "Framework64"... but remember SSDT is only 32bit!)
Then run that and it's all good right? Well, I thought so to, but then this error appeared.
C:\Users\Richard\Source\db_.sqlproj(59,3): error MSB4019: The imported project "C:\Program Files (x86)\MSBuild\Microsoft\VisualStudio\v11.0\SSDT\Microsoft.Data.Tools.Schema.SqlTasks.targets" was not found. Confirm that the path in the <Import> declaration is correct, and that the file exists on disk.
Colour me confused? Well, the error points towards a line in the sqlproj file. So I unloaded the sqlproj in Visual Studio and opened up the file in xml editor to see the offending command.
So in case you were not already aware of this, a proj file is basically a MSBuild target file that tells MSBuild what to build. You can even see the properties set for each build configuration. And the lines that are causing an issue here are just some nasty business.
What is happening is that when a project is built in Visual Studio some targets that are external of the whole process that were installed as part of Visual Studio are used as part of the process. Obviously, when we run MSBuild via cmdline, we are not setting the parameter "VisualStudioVersion", because this is pretty much a "headless" build. So the sqlproj file handles this by setting the parameter to 11.0, which is pretty horrible. Given that projects are created in Visual Studio, I would've felt that the version a project was created in would be baked in as the default, as opposed to just some random version number. At the very least, a warning that a default version number has been set would come in useful, especially as this is 2016 now and the sqlproj files default to 2010 targets.
Anyway, we can include a workaround by passing in the VisualStudioVersion like so
If you're still getting the error, double check that you can compile the solution in Visual Studio and ensure that you have SQL Server Data Tools installed on your machine.