I was having this issue building a SQL Server project on a CI/CD pipeline. In fact, I was having it locally as well, and I did not manage to solve it.
What worked for me was using an MSBuild SDK, capable of producing a SQL Server Data-Tier Application package (.dacpac
) from a set of SQL scripts, which implies creating a new project. But I wanted to keep the SQL Server project, so that I could link it to the live database through SQL Server Object Explorer on Visual Studio. I took the following steps to have this up and running:
.sql
database scripts.Set the contents of the .csproj
as follows:
<?xml version="1.0" encoding="utf-8"?> <Project Sdk="MSBuild.Sdk.SqlProj/1.0.0"> <PropertyGroup> <SqlServerVersion>Sql140</SqlServerVersion> <TargetFramework>netstandard2.0</TargetFramework> </PropertyGroup> </Project>
I have chosen Sql140 as the SQL Server version because I am using SQL Server 2019. Check this answer to find out the mapping to the version you are using.
Ignore the SQL Server project on build, so that it stops breaking locally (it does build on Visual Studio, but it fails on VS Code).
Now we just have to make sure the .sql
files are inside the SDK project when it is built. I achieved that with a simple powershell routine on the CI/CD pipeline that would copy the files from the SQL Server project to the SDK project:
Copy-Item -Path "Path.To.The.Database.Project\dbo\Tables\*" -Destination (New-item -Name "dbo\Tables" -Type Directory -Path "Path.To.The.DatabaseSDK.Project\")
PS: The files have to be physically in the SDK project, either in the root or on some folder, so links to the .sdk
files in the SQL Server project won't work. In theory, it should be possible to copy these files with a pre-build condition, but for some obscure reason, this was not working for me. I tried also to have the .sql
files on the SDK project and link them to the SQL Server project, but that would easily break the link with the SQL Server Object Explorer, so I decided to drop this as well.