TeamCity SQL Project : Microsoft.Data.Tools.Schema.SqlTasks.targets was not found

Question

TeamCity

I have a TeamCity project with a .NET CLI (dotnet) build step for a Visual Studio Solution ( .sln ).

The solution includes a SQL Project ( .sqlproj ).

Running this build-step gives error :

[build] D:\TeamCity\buildAgent\work\e9ff385151b10e4c\Services\AccountCharacteristics\Database-RoundHouse-Deploy1\Database\Database.sqlproj(67,3): error MSB4019: The imported project "C:\Program Files\dotnet\sdk\2.2.101\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.

Database.sqlproj

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

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

So it appears that SQLDBExtensionsRefPath = C:\Program Files\dotnet\sdk\2.2.101\Microsoft\VisualStudio\v11.0\SSDT\

Apparently the .sqlproj defaults to 11.0 -- a very old version of Visual Studio.

How does SQLDBExtensionsRefPath get set ? How can I install/config the agent for the correct path ?

My agent has :

C:\Program Files (x86)\Microsoft Visual Studio\2019\Professional\MSBuild\Microsoft\VisualStudio\v16.0\SSDT\Microsoft.Data.Tools.Schema.SqlTasks.targets

Also I notice that a NuGet package has been released :

https://www.nuget.org/packages/Microsoft.Data.Tools.Msbuild/

But it's not clear how to use it. It seems clear I need to reference the package in the VS .csproj. But then how to ensure that Visual Studio and Team City point to the Microsoft.Data.Tools.Schema.SqlTasks.targets in the package ( under the packages folder ) ?

Answer 1

Set it as an environment variable with setx SQLDBExtensionsRefPath "C:\Program Files (x86)\Microsoft Visual Studio\2019\Professional\MSBuild\Microsoft\VisualStudio\v16.0\SSDT"

or by adding via the 'environment variables' in the Windows UI

See https://learn.microsoft.com/en-us/archive/blogs/ssdt/part-5-use-your-own-build-and-deployment-agent