Build sqlproj projects with MSBuild on a Build Server
God bless Microsoft, each time a new Visual Studio comes out, they make an improvement, like making the install directories more logical and allowing better side-by-side installations. The problem? Most of these are not backwards compatible and it creates a whole load of compatibility problems.
Install VS 2017 and create a database project (sqlproj) in a solution. Open up the sqlproj file and you will see some really poorly thought out targets:
Basically, what this says is that if I don't know what the visual studio version is when I build, then I will assume that I should look for v11 (VS2012) directories and fail if I don't find them rather than what most people would do, which would be either to fail if the version is not passed in, or to hard-code the version you chose when you added the project.
Run this on a build server with MSBuild instead of Visual Studio and you might see the following error:
The imported project "C:\Program Files (x86)\Microsoft Visual Studio\2017\BuildTools\MSBuild\Microsoft\VisualStudio\v11.0\SSDT\Microsoft.Data.Tools.Schema.SqlTasks.targets" was not found
Which makes sense because I don't have VS2012 installed on the build server at all.
Well, it looks the same but this time it should work, since I have v15 installed. I had a look and sure enough, the SQL tools were not installed. Installations have changed in VS2017 and although I tried to install the Data Processing workload for the Build Tools, the option was not there. I installed the workload using the VS2017 community edition, checked for the target file, which was now there but the build failed again.
Install VS 2017 and create a database project (sqlproj) in a solution. Open up the sqlproj file and you will see some really poorly thought out targets:
<PropertyGroup>
<VisualStudioVersion Condition="'$(VisualStudioVersion)' == ''">11.0</VisualStudioVersion>
<!-- Default to the v11.0 targets path if the targets file for the current VS version is not found -->
<SSDTExists Condition="Exists('$(MSBuildExtensionsPath)\Microsoft\VisualStudio\v$(VisualStudioVersion)\SSDT\Microsoft.Data.Tools.Schema.SqlTasks.targets')">True</SSDTExists>
<VisualStudioVersion Condition="'$(SSDTExists)' == ''">11.0</VisualStudioVersion>
</PropertyGroup>
Basically, what this says is that if I don't know what the visual studio version is when I build, then I will assume that I should look for v11 (VS2012) directories and fail if I don't find them rather than what most people would do, which would be either to fail if the version is not passed in, or to hard-code the version you chose when you added the project.
Run this on a build server with MSBuild instead of Visual Studio and you might see the following error:
The imported project "C:\Program Files (x86)\Microsoft Visual Studio\2017\BuildTools\MSBuild\Microsoft\VisualStudio\v11.0\SSDT\Microsoft.Data.Tools.Schema.SqlTasks.targets" was not found
Which makes sense because I don't have VS2012 installed on the build server at all.
I eventually realised the issue is that VS injects the version into the target whereas MSBuild does not. A simple parameter passed to MSBuild (/p:VisualStudioVersion=15.0) sorts that problem and tells it to use VS2017, which I have installed on the server, although only the Build Tools.
I then get a different error:
The imported project "C:\Program Files (x86)\Microsoft Visual Studio\2017\BuildTools\MSBuild\Microsoft\VisualStudio\v15.0\SSDT\Microsoft.Data.Tools.Schema.SqlTasks.targets" was not found
Looking closer, I noticed that the path was almost correct. MSBuild uses the BuildTools subdirectory of 2017 whereas proper Visual Studio uses community (in my case). Basically, there is no obvious way to install SSDT into the Build Tools area, which is where MSBuild looks so instead I copied over the MSBuild\Microsoft\VisualStudio\v15.0\SSDT folder from community into buildtools (with its directory structure) and also copied over Common7\IDE\Extensions\Microsoft\SQL* directories, which are used by the sqlproj target and the build worked!