How to build .sqlproj requiring SSDT in a linux docker container?

Question

I want to build a .sqlproj inside a linux container. The problem is that building the .sqlproj is dependent on SSDT and so far I can't find SSDT that can be installed as standalone on linux.

Error I see running 'dotnet msbuild' in my container:

error MSB4019: The imported project "/usr/share/dotnet/sdk/2.2.402/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.

Searching the .sqlproj file for the issue, I see we are trying to import a Schema.SqlTasks.targets file, which I'm assuming SSDT creates:

<Import Condition="'$(SQLDBExtensionsRefPath)' == ''" Project="$(MSBuildExtensionsPath)\Microsoft\VisualStudio\v$(VisualStudioVersion)\SSDT\Microsoft.Data.Tools.Schema.SqlTasks.targets" />

I see the same error testing out 'dotnet build'

There is a Windows standalone option:

https://learn.microsoft.com/en-us/sql/ssdt/download-sql-server-data-tools-ssdt?redirectedfrom=MSDN&view=sql-server-ver15

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

Has anyone found a way to provide SSDT in a linux container?

Goal State: The build step to generate the dacpacs would occur inside the container.

Current State: For now I'm using Visual Studio on my machine as the build machine, then copying the dacpacs up to the container where sqlpackage.exe can then publish the schema.

Why Linux? This dockerized DB will support a stack of services running in linux containers, so a windows container is not ideal.

Benson

I think this is your best bet github.com/jmezach/MSBuild.Sdk.SqlProj. – Holmen

I've just been hit by the same problem. Following the instructions at the above github, allowed my SQL project to be built using the Dotnet CLI, but I can no longer load the project into Visual Studio for editing. – Commentative

Answer 1

Ok, so with a bit of help from ErikEJ and other members of the community, at the GitHub link given to you by Brett Rowberry in the comments, I finally figured out how to do this.

The steps to follow are quite simple.

1. Add your SQL Server project
2. Add a .NET standard class library project and call it something like "database.build"
3. remove all the code from the class library project, and modify the csproj file so it reads something like the following

4. Change the properties on your solution so as to NOT build the sql server project for all configurations.

Once you done this, you'll find that you still get access to the SQL database project in visual studio, and get all the syntax highlighting and intellisense, but your CI will build the SQL code via the linked class library and produce a dacpac in it's output folder ready to be deployed.

Right clicking on the project will allow you to build it from within Visual Studio, and right clicking and selecting "Publish" will allow you to specify your database parameters and publish it to the DB server being used. If you have other objects already in your database that you need to reference, you can create an object only dacpac from the database, using SQL server management studio, which you can then add to your project and check in with it's sources so that you do not need to recreate every single object you may already have.

I've written a blog post explaining it all at length here: https://shawtyds.wordpress.com/2020/08/26/using-a-full-framework-sql-server-project-in-a-net-core-project-build/

Commentative

Answer 2

I had the same issue what I did as a workaround:

1- Generated sql script from database project "Generate Script"

2- Followed this code sample to run the script on the Linux container

https://github.com/microsoft/mssql-docker/issues/2#issuecomment-547699532

https://github.com/lkurzyniec/netcore-boilerplate/tree/master/db/mssql

https://github.com/lkurzyniec/netcore-boilerplate/blob/master/docker-compose.yml

Policewoman