Visual Studio SSDT Database project (.sqlproj) build - generate .dacpac using CLI without MSBUILD (msbuild.exe)

Question

Looks as though dotnet CLI has no support for Database projects (.sqlproj) according to this: https://github.com/dotnet/sdk/issues/8546

in my case dotnet build fails with the following error:

C:...*.Database.sqlproj(59,3): error MSB4019: The imported project "C:\Program Files\dotnet\sdk\3.1.301\Microsoft\VisualStudio\v11.0\SSDT\Microsoft.Data.Tools.Schema.SqlTasks.targets" was not found. Confirm that the expression in the Import declaration "C:\Program Files\dotnet\sdk\3.1.301\Microsoft\VisualStudio\v11.0\SSDT\Microsoft.Data.Tools.Schema.SqlTasks.targets" is correct, and that the file exists on disk.

Needless to say, the solution compiles and generates .dacpac via Build in Visual Studio. I need this to work from command line. Do I have any solutions other than msbuild.exe? Perhaps some lovely nuget package out there that could help?

Answer 1

I have managed to make it work as described in this article: https://erikej.github.io/efcore/2020/05/11/ssdt-dacpac-netcore.html

It requires a special type of .NET Standard Visual Studio project - MSBuild.Sdk.SqlProj (nuget download), which copies scripts from the Database project when compiled.

here's my build_database.cmd code:

SET db_project=Database.Build
dotnet tool install -g dotnet-script  
cd %db_project%
dotnet build
dotnet-script Program.csx 

And a C# script (Program.csx), which creates and deploys a .dacpac using Microsoft.SqlServer.DACFx nuget package:

#r "nuget: Microsoft.SqlServer.DACFx, 150.4769.1"
using Microsoft.SqlServer.Dac;

var dbName = "SampleDatabase";
var connectionString = $"Data Source=.;Initial Catalog={dbName};Integrated Security=True;";
var dacpacLocation = Directory.GetFiles(@".\bin", "Database.Build.dacpac", 
                                                           SearchOption.AllDirectories)[0];
var dbPackage = DacPackage.Load(dacpacLocation);
var services = new DacServices(connectionString);

services.Deploy(dbPackage, dbName, true); 

this approach is fully automated and can be used in either CI or local dev environment and, apparently, is also cross-platform (albeit I've only tested on Windows). Hope this helps :)