How to build .sqlproj projects using "dotnet" tool?

Question

Unable to build .Net .sqlproj using "dotnet" command line tool. Here's the error:

dotnet\sdk\3.1.300\Microsoft\VisualStudio\v11.0\SSDT\Microsoft.Data.Tools.Schema.SqlTasks.targets" was not found

I have installed SSDT tool but it's not available at the above location.

Note: It's buildings without any error using Visual Studio and MSBuild

Iatric

What's the specific reason that you want to use dotnet build .sqlproj? Why not use VS build task/msbuild task in azure-devops for CI? – Mansoor

We find dotnet CLI more mature in term of commands and it's parameter but it appears it can't resolve many dependencies. We might need to switch to some other CLI e.g MSBuild. – Iatric

Answer 1

How to build .sqlproj projects using “dotnet” tool?

Sorry but as I know this is not supported scenario for now. It's by design that dotnet cli can't build the Database Project with old-framework style project file.

As for the reason why it's buildings without any error using Visual Studio and MSBuild but failing using dotnet. Please check your SSDT installation path, it should be under pathC:\Program Files (x86)\Microsoft Visual Studio\2019\VSEditon\MSBuild\Microsoft\VisualStudio\v16.0\SSDT.

The SSDT is installed as a sub-module of MSBuild(VS build tools) or MSBuild from VS, so it works well when building it via VS/MSbuild. But dotnet cli won't know where to find this module which is under MSBuild folder's sub-folder, so it throws error.

There's once a discussion about this in github, in my opinion the feature to support build SQLProj via Dotnet hasn't come true. If you're interested in that feature, I suggest you can open a new ticket there to post your idea.

Mansoor

Thanks for your answer. You maybe right but why we don't have such support in dotnet CLI because this is very basic dependency to build project. Actually I'm working on something generic which can build all type of projects using dotnet CLI but it appears it won't support all type of projects. We have more examples where some tools dependency is not getting resolved in dotnet CLI. E.g 'dotnet\sdk\3.1.100\Microsoft\VisualStudio\v16.0\WebApplications\Microsoft.WebApplication.targets" was not found' – Iatric

I know such dependency can be resolved but do we need to place all such tools every time? OR a better solution is to use MSBuild? – Iatric

Hmm, I think a better solution is to use Msbuild. It supports build projects like C++, C#(.net core/standard/framework),VB.net, database project... while dotnet for now is more suitable for .net core and .net standard projects(new SDK format projects). Also, dotnet build can't support C++ and other languages... Unless your whole solution targets .net core/standard project, I don't recommend dotnet :-) – Mansoor

@Iatric Of course it would be better if dotnet core can build all project types well, but for now it's just not supported yet. You can post your idea here to let the team know, hope it helps~ – Mansoor

okay and then what exactly the difference between dotnet vs MSBuild? It's a altogether different question but I think this will help people understand that which one to choose if they are starting from scratch. – Iatric

@Iatric It's a bit board cause it must need many details about the difference. In my opinion, 1.msbuild works for windows while dotnet cli works cross-platform(even in linux). 2.Msbuild can build almost all the project types which supported by VS IDE(it's build engine of VS IDE) while dotnet cli only supports projects like .net core and standard, maybe also part .net framework. So I choose .net core when I need to work cross-platform, choose VS build/msbuild when I'm working on projects from VS (And if I'm happy, I can also use dotnet to build those .net core/standard) – Mansoor

Also, maybe this link is also helpful for your puzzle. – Mansoor

Answer 2

Some progress has been made, preview has been released https://github.com/microsoft/DacFx/tree/main/src/Microsoft.Build.Sql

<Sdk Name="Microsoft.Build.Sql" Version="0.1.3-preview" />

Gid

Answer 3

As of March 2020 this is now a possibility using the MSBuild.Sdk.SqlProj package. Technically it's not building the sqlproj file itself, but an equivalent version based on a csproj to produce a DACPAC. It currently has some advantages, such as cross-platform builds, and being able to build a whole folder instead of leaving out that one file that you always forget to manually add to the sqlproj. Current disadvantages are a slower build due to no dbmdl caching, and less IDE/tooling support at least for importing changes from an external database or DACPAC into the project source.

Antigorite

is this a MSFT supported project? – Footling

@Footling not technically - Of course MS had to go do their own thing instead; see Arun's answer. (I'm not sure why it's not more upvoted.) But I'm sticking with the community project for now. – Antigorite

Answer 4

Microsoft has released SQL Database Project extension (preview) for compile using dotnet command

dotnet build "sqlproj file path" /p:NetCoreBuild=true /p:NETCoreTargetsPath="DotNet Core build folder"

https://learn.microsoft.com/en-us/sql/azure-data-studio/extensions/sql-database-project-extension-build-from-command-line?view=sql-server-ver15

Geryon