Sql-docs: The Microsoft.Data.Tools.Msbuild nuget package does not seem to work

[eanyanwu]

If this is not the correct place to flag this, please let me know (and maybe point me towards someone who can help 🙇 )

Description

According to this announcment, I do not need to download the full Sql Server Data Tools (SSDT) to build a SQL project (i.e .sqlproj) This is useful for build server scenarios where I don't want visual studio or SSDT on the machine.

The alternative that is presented is downloading the Microsoft.Data.Tools.Msbuild nuget package, setting two environment variables and voila.

Problem

After following the steps for using SSDT and executing dotnet msbuild <SQL_PROJECT> I get the following error

( https://devblogs.microsoft.com/ssdt/part-5-use-your-own-build-and-deployment-agent/ , dated 2016/08/22 )

C:\Users\EzeanyinabiaAnyanwu\Source\repos\BuildingSQLProjectRepro\Microsoft.Data.Tools.Msbuild\lib\net46\Microsoft.Data.Tools.Schema.SqlTasks.targets(477,5): error MSB4061: The "SqlModelResolutionTask" task could not be instantiated from "C:\Users\EzeanyinabiaAnyanwu\Source\repos\BuildingSQLProjectRepro\Microsoft.Data.Tools.Msbuild\lib\net46\\Microsoft.Data.Tools.Schema.Tasks.Sql.dll".  [C:\Users\EzeanyinabiaAnyanwu\Source\repos\BuildingSQLProjectRepro\BuildingSQLProjectRepro\BuildingSQLProjectRepro.sqlproj]
C:\Users\EzeanyinabiaAnyanwu\Source\repos\BuildingSQLProjectRepro\Microsoft.Data.Tools.Msbuild\lib\net46\Microsoft.Data.Tools.Schema.SqlTasks.targets(477,5): error MSB4061: System.TypeInitializationException: The type initializer for 'Microsoft.Data.Tools.Schema.Tasks.Sql.DataTask' threw an exception. ---> System.TypeLoadException: Could not load type 'System.Diagnostics.Eventing.EventDescriptor' from assembly 'System.Core, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'. [C:\Users\EzeanyinabiaAnyanwu\Source\repos\BuildingSQLProjectRepro\BuildingSQLProjectRepro\BuildingSQLProjectRepro.sqlproj]
C:\Users\EzeanyinabiaAnyanwu\Source\repos\BuildingSQLProjectRepro\Microsoft.Data.Tools.Msbuild\lib\net46\Microsoft.Data.Tools.Schema.SqlTasks.targets(477,5): error MSB4061:    at Microsoft.Data.Tools.Diagnostics.Tracer.WriteEtwEvent(TraceEventType eventType, TraceId traceId, String message) [C:\Users\EzeanyinabiaAnyanwu\Source\repos\BuildingSQLProjectRepro\BuildingSQLProjectRepro\BuildingSQLProjectRepro.sqlproj]
C:\Users\EzeanyinabiaAnyanwu\Source\repos\BuildingSQLProjectRepro\Microsoft.Data.Tools.Msbuild\lib\net46\Microsoft.Data.Tools.Schema.SqlTasks.targets(477,5): error MSB4061:    at Microsoft.Data.Tools.Diagnostics.Tracer.TraceEvent(TraceEventType eventType, TraceId traceId, String message) [C:\Users\EzeanyinabiaAnyanwu\Source\repos\BuildingSQLProjectRepro\BuildingSQLProjectRepro\BuildingSQLProjectRepro.sqlproj]
C:\Users\EzeanyinabiaAnyanwu\Source\repos\BuildingSQLProjectRepro\Microsoft.Data.Tools.Msbuild\lib\net46\Microsoft.Data.Tools.Schema.SqlTasks.targets(477,5): error MSB4061:    at Microsoft.Data.Tools.Schema.Extensibility.ExtensionTypeLoader.LoadTypes() [C:\Users\EzeanyinabiaAnyanwu\Source\repos\BuildingSQLProjectRepro\BuildingSQLProjectRepro\BuildingSQLProjectRepro.sqlproj]
C:\Users\EzeanyinabiaAnyanwu\Source\repos\BuildingSQLProjectRepro\Microsoft.Data.Tools.Msbuild\lib\net46\Microsoft.Data.Tools.Schema.SqlTasks.targets(477,5): error MSB4061:    at Microsoft.Data.Tools.Schema.Tasks.Sql.DataTask..cctor() [C:\Users\EzeanyinabiaAnyanwu\Source\repos\BuildingSQLProjectRepro\BuildingSQLProjectRepro\BuildingSQLProjectRepro.sqlproj]
C:\Users\EzeanyinabiaAnyanwu\Source\repos\BuildingSQLProjectRepro\Microsoft.Data.Tools.Msbuild\lib\net46\Microsoft.Data.Tools.Schema.SqlTasks.targets(477,5): error MSB4061:    --- End of inner exception stack trace --- [C:\Users\EzeanyinabiaAnyanwu\Source\repos\BuildingSQLProjectRepro\BuildingSQLProjectRepro\BuildingSQLProjectRepro.sqlproj]
C:\Users\EzeanyinabiaAnyanwu\Source\repos\BuildingSQLProjectRepro\Microsoft.Data.Tools.Msbuild\lib\net46\Microsoft.Data.Tools.Schema.SqlTasks.targets(477,5): error MSB4061:    at Microsoft.Data.Tools.Schema.Tasks.Sql.SqlModelResolutionTask..ctor() [C:\Users\EzeanyinabiaAnyanwu\Source\repos\BuildingSQLProjectRepro\BuildingSQLProjectRepro\BuildingSQLProjectRepro.sqlproj]
C:\Users\EzeanyinabiaAnyanwu\Source\repos\BuildingSQLProjectRepro\Microsoft.Data.Tools.Msbuild\lib\net46\Microsoft.Data.Tools.Schema.SqlTasks.targets(477,5): error MSB4060: The "SqlModelResolutionTask" task has been declared or used incorrectly, or failed during construction. Check the spelling of the task name and the assembly name. [C:\Users\EzeanyinabiaAnyanwu\Source\repos\BuildingSQLProjectRepro\BuildingSQLProjectRepro\BuildingSQLProjectRepro.sqlproj]

I was able to reproduce the problem with a bare SQL project created using Visual Studio 2019. Here it is.
The folder has a script called repro.ps1 to execute all the steps I took and hopefully demonstrate the error. cd into the folder that contains the script before executing it.

This is the output of my dotnet --info

.NET Core SDK (reflecting any global.json):
 Version:   2.1.701
 Commit:    8cf7278aa1

Runtime Environment:
 OS Name:     Windows
 OS Version:  10.0.17763
 OS Platform: Windows
 RID:         win10-x64
 Base Path:   C:\Program Files\dotnet\sdk\2.1.701\

Host (useful for support):
  Version: 2.1.12
  Commit:  ccea2e606d

.NET Core SDKs installed:
  1.1.0 [C:\Program Files\dotnet\sdk]
  1.1.5 [C:\Program Files\dotnet\sdk]
  1.1.6 [C:\Program Files\dotnet\sdk]
  1.1.7 [C:\Program Files\dotnet\sdk]
  1.1.8 [C:\Program Files\dotnet\sdk]
  1.1.9 [C:\Program Files\dotnet\sdk]
  1.1.10 [C:\Program Files\dotnet\sdk]
  1.1.13 [C:\Program Files\dotnet\sdk]
  2.1.2 [C:\Program Files\dotnet\sdk]
  2.1.3 [C:\Program Files\dotnet\sdk]
  2.1.4 [C:\Program Files\dotnet\sdk]
  2.1.104 [C:\Program Files\dotnet\sdk]
  2.1.200 [C:\Program Files\dotnet\sdk]
  2.1.202 [C:\Program Files\dotnet\sdk]
  2.1.400 [C:\Program Files\dotnet\sdk]
  2.1.402 [C:\Program Files\dotnet\sdk]
  2.1.505 [C:\Program Files\dotnet\sdk]
  2.1.602 [C:\Program Files\dotnet\sdk]
  2.1.700 [C:\Program Files\dotnet\sdk]
  2.1.701 [C:\Program Files\dotnet\sdk]

.NET Core runtimes installed:
  Microsoft.AspNetCore.All 2.1.2 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.All]
  Microsoft.AspNetCore.All 2.1.4 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.All]
  Microsoft.AspNetCore.All 2.1.9 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.All]
  Microsoft.AspNetCore.All 2.1.11 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.All]
  Microsoft.AspNetCore.All 2.1.12 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.All]
  Microsoft.AspNetCore.App 2.1.2 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.App]
  Microsoft.AspNetCore.App 2.1.4 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.App]
  Microsoft.AspNetCore.App 2.1.9 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.App]
  Microsoft.AspNetCore.App 2.1.11 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.App]
  Microsoft.AspNetCore.App 2.1.12 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.App]
  Microsoft.NETCore.App 1.0.5 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App]
  Microsoft.NETCore.App 1.0.8 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App]
  Microsoft.NETCore.App 1.0.9 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App]
  Microsoft.NETCore.App 1.0.10 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App]
  Microsoft.NETCore.App 1.0.11 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App]
  Microsoft.NETCore.App 1.0.12 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App]
  Microsoft.NETCore.App 1.0.15 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App]
  Microsoft.NETCore.App 1.1.2 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App]
  Microsoft.NETCore.App 1.1.5 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App]
  Microsoft.NETCore.App 1.1.6 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App]
  Microsoft.NETCore.App 1.1.7 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App]
  Microsoft.NETCore.App 1.1.8 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App]
  Microsoft.NETCore.App 1.1.9 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App]
  Microsoft.NETCore.App 1.1.12 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App]
  Microsoft.NETCore.App 2.0.3 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App]
  Microsoft.NETCore.App 2.0.4 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App]
  Microsoft.NETCore.App 2.0.5 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App]
  Microsoft.NETCore.App 2.0.6 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App]
  Microsoft.NETCore.App 2.0.7 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App]
  Microsoft.NETCore.App 2.0.9 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App]
  Microsoft.NETCore.App 2.1.2 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App]
  Microsoft.NETCore.App 2.1.4 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App]
  Microsoft.NETCore.App 2.1.9 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App]
  Microsoft.NETCore.App 2.1.11 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App]
  Microsoft.NETCore.App 2.1.12 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App]

Thank you for your time!

[MightyPen]

@eanyanwu Hello Eze, This GitHub Issues website is only for shortcomings in the Microsoft SQL documentation, as published on https://docs.microsoft.com/sql/......
Perhaps one of the following links might be helpful:

• SQL Server Data Tools - MSDN forum
• Support options for business users
• Contact Microsoft
• All the ways to get help

Thank you for submitting this Issue. We hope to hear from you again.