Headless install of SQL Server Data Tools (SSDT) for Visual Studio 2017

Question

For a build server, we need a headless install of SQL Server Data Tools, for Visual Studio 2017.

Our build servers already have "Build Tools for Visual Studio 2017", which provides nice support for headless builds. But any solutions with SSDT projects fail due to missing MSBuild targets, eg:

[MSBuild] src\Database\Upfront.Database.sqlproj: Build default targets [10:27:46][src\Database\Upfront.Database.sqlproj] E:\BuildAgent\work\7769fbf76d8b9008\src\Database\Upfront.Database.sqlproj(56, 3): error MSB4019: The imported project "C:\Program Files (x86)\Microsoft Visual Studio\2017\Enterprise\MSBuild\Microsoft\VisualStudio\v15.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.

First, I tried modifying the set of components installed by "Build Tools for Visual Studio 2017", but no SSDT components appear to be available.

Second, I tried installing SSDT for Visual Studio 2017, which didn't seem to install correctly into the "Build Tools for VS 2017" profile. It does allow me to create a new VS 2017 profile, eg SQL, so the required MSBuild files are installed under C:\Program Files (x86)\Microsoft Visual Studio\2017\SQL\; however it alters my msbuild environment variables so that "Build Tools for VS 2017" files (which are in C:\Program Files (x86)\Microsoft Visual Studio\2017\BuildTools) aren't found, resulting in C# projects being un-buildable.

It seems that "SSDT for Visual Studio 2017" and "Build Tools for Visual Studio 2017" aren't compatible with each other. Has anyone been able to get them to work together? Or has anyone found a better way to build SSDT projects on build servers?

SSDT has been pretty great, except the build server support, which is a pain every time.

Answer 1

Build support for ssdt database projects is now available as a nuget package for this exact scenario.

This has all the details on how to configure a build server for ssdt database projects:

https://blogs.msdn.microsoft.com/ssdt/2016/08/22/part-5-use-your-own-build-and-deployment-agent/

The gist of it is that you download the nuget package and extract it somewhere then set a couple of environment variables to point to the package contents and you should be fine to go ahead and build without visual studio.

Ed

Answer 2

After you install SSDT for Visual Studio 2017 on the server, you need to set an environment variable called SQLDBExtensionsRefPath to "C:\Program Files (x86)\Microsoft Visual Studio\2017\SQL\MSBuild\Microsoft\VisualStudio\v15.0\SSDT". This environment variable is used by the following lines in the .sqlproj file:

<Import Condition="'$(SQLDBExtensionsRefPath)' != ''" Project="$(SQLDBExtensionsRefPath)\Microsoft.Data.Tools.Schema.SqlTasks.targets" />
<Import Condition="'$(SQLDBExtensionsRefPath)' == ''" Project="$(MSBuildExtensionsPath)\Microsoft\VisualStudio\v$(VisualStudioVersion)\SSDT\Microsoft.Data.Tools.Schema.SqlTasks.targets" />

Now msbuild will be able to find the .targets files, but it will still look for the IDE extension in the BuildTools folder instead of the SQL folder. I think that setting the SSDTPath environment variable was supposed to resolve this (you can try setting it to "C:\Program Files (x86)\Microsoft Visual Studio\2017\SQL\Common7\IDE\Extensions\Microsoft\SQLDB\Dac\140" and see if your build works), but I was still getting errors because msbuild was unable to locate the master.dacpac file.

In the end, I modified the .sqlproj file to fix the SSDTExists property and modify the VsInstallRoot property. I changed the following lines:

<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>

to:

<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') Or '$(SQLDBExtensionsRefPath)' != ''">True</SSDTExists>
  <VisualStudioVersion Condition="'$(SSDTExists)' == ''">11.0</VisualStudioVersion>
</PropertyGroup>
<PropertyGroup>
  <!-- In the case that SSDT was installed separately from Visual Studio (e.g. for the Visual Studio build tools), change the VsInstallRoot so that the IDE extension will be found. -->
  <VsInstallRoot Condition="!Exists('$(VsInstallRoot)\Common7\IDE\Extensions\Microsoft\SQLDB') And Exists('$(VsInstallRoot)\..\SQL')">$(VsInstallRoot)\..\SQL</VsInstallRoot>
</PropertyGroup>

I did not experience the problem that you had where msbuild environment variables got messed up by the SSDT installation, so everything is running smoothly on my build server with just these two changes.