I'm building an ASP.NET Core 2.0 Web API application that is hosted in an Ubuntu environment. So far, I've had great success getting things building and running (for the .NET Core app) in Ubuntu.
For the database, I have a SqlProj included in my solution. The project includes typical things such as tables, SPs, and pre/post deployment scripts. I'm using the following command (on my Windows-based dev machine) to build and deploy this project:
msbuild .\MyProject.DB.sqlproj /t:Build /t:Publish /P:SqlPublishProfilePath="./PublishProfiles/MyProject.DB.publish.xml"
When I take this approach, everything builds and deploys properly; however, since I will be taking advantage of the .NET Core CLI commands + CI/CD that targets an Ubuntu environment, I'd like to do something more like:
dotnet msbuild .\MyProject.DB.sqlproj /t:Build /t:Publish /P:SqlPublishProfilePath="./PublishProfiles/MyProject.DB.publish.xml"
In Windows, I immediately get the error:
error MSB4019: The imported project "C:\Program Files\dotnet\sdk\2.1.4\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.
Basically, what I'm asking is how to successfully build and deploy a SqlProj project in an Ubuntu environment. I've tried Googling, but I have had zero luck thus far. All of the similar issues that I've found were for individuals who were editing their .proj file to target their VS folder's SSDT. All of these individuals were fixing the issue in Windows. This approach will not work in Ubuntu, since the targets file uses Windows registry keys.
EDIT: I'm aware that SSDT is needed in order to perform such a deployment using MSBuild. I've found no evidence that installing/using SSDT is even possible in Ubuntu. If it is not, perhaps there is an alternative solution?
FYI, I'm aware that using a code-first approach with EF Core is possible. I'm attempting to take the raw SP approach (along with leveraging indexes) and keep track of all of my code using SqlProj instead. This will all be stored and CI/CDed from a Git repo.
I would like to revisit this in 2020 with an updated answer to my original question.
I have taken a different approach to building an deploying SQL Server projects. My current approach is to build a pipeline that uses a vs2017-win2016 agent and use this to build a .dacpac. From there, you build a deployment pipeline to deploy the dacpac (from your artifact drop) out to the SQL Server instance.
This approach better accommodates DevOps methodologies and removes the manual process associated with my previous solution.
You can read more about this here: