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

user_115466 0
Viewed 2390

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?

dacpac dotnet-cli visual-studio msbuild .net
3 Answers

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

user_1154660

Do I have any solutions other than msbuild.exe? Perhaps some lovely nuget package out there that could help?

Actually, there is no other build tool to build Database projects except msbuild.exe. So you have to use msbuild.exe.

Since VS2017, msbuild.exe has a separate version that does not depend on VS IDE called Build Tool for VS. And it is the build command line that integrates with the VS Workload environment.

So I think you want a lightweight build tool on build server rather than the huge VS IDE, if so, you should download it.

Suggestion

1) download build tool for VS2019 under All Downloads-->Tools for Visual Studio 2019-->Build Tool for Visual Studio 2019.

enter image description here

2) remember to select the corresponding workload Data storage and processing build tools

enter image description here

When you finish it, you can build your database project with it.

user_124507470

My method:

  1. Install Visual Studio Build Tools 2019

Include "Data storage and processing build tools" workflow and .NET Framework 4.6.1 targeting pack (individual component)

user_142744860
Related