The package often suggested by others, R&R WFM's MSBuild.Sdk.SqlProj does not come from Microsoft, and it doesn't really do a "real SSDT build". It does not use SSDT build tools, it is a separate 3rd party tool that can generate limited DACPAC packages with reduced features. This info does not come from my experience with that tool. I read it somewhere when I tried to research what options I have left as I'm stuck with DACPACs and Linux. While I can't confirm that info in any way, it looks true, repo doesn't have 'Microsoft' logos anywhere, and I read such warnings on multiple unrelated sites, posted by supposedly different people.
That being said, it's an awesome tool that certainly required huge amount of workand knowledge.
But there's another one as well.
Recently Microsoft started developing their own cross-platform SSDT build tool, Microsoft.Build.Sql coming from DacFx. At the time of writing this, it's still in preview and the current version is 0.1.10. You'll often see 0.1.3 or 0.1.7 if you search for articles about how to use that.
Microsoft provided a nice guide how to convert an existing .sqlproj generated by VisualStudio+SSDT. It's concise, a bit too concise, and requires minor obvious fixes. The article also points to a minimalistic new-style .sqlproj file, the link is broken, but the file resides in a nearby folder.
The article mentions that new-style projects support globbing (so Include="folder/**/*.sql" instead of listing all files), and that including all SQL files is already the default behavior, so an 'empty' project file like the sample one will grab all SQL files - but be careful, and read the final paragraph about compatibility with VisualStudio.
Using this info, I was able to convert my .sqlproj file. I did the steps explained there, and also I didn't remove any now-not-needed file inclusions, so VS is happy.
My .sqlproj file looks now like that:
<?xml version="1.0" encoding="utf-8"?>
<Project DefaultTargets="Build" ToolsVersion="4.0">
<!-- IMPORTANT: this is a PREVIEW tooling, keep an eye on changes and notes -->
<!-- https://github.com/microsoft/DacFx/blob/main/src/Microsoft.Build.Sql/docs/Converting-Existing.md -->
<Sdk Name="Microsoft.Build.Sql" Version="0.1.10-preview" />
<PropertyGroup>
<Name>xxxxxxxxxxxxxxxx</Name>
<DSP>Microsoft.Data.Tools.Schema.Sql.Sql130DatabaseSchemaProvider</DSP>
<ModelCollation>1033,CI</ModelCollation>
<TargetDatabaseSet>True</TargetDatabaseSet>
<DefaultCollation>Latin1_General_100_CI_AS</DefaultCollation>
</PropertyGroup>
<ItemGroup>
<Folder Include="Properties" />
<Folder Include="Tables" />
<Folder Include="Views" />
<Folder Include="Procedures" />
</ItemGroup>
<ItemGroup>
<Build Include="Tables\xxxxxxxxx.sql" />
<Build Include="Tables\xxxxxxxxx.sql" />
<Build Include="Views\xxxxxxxxx.sql" />
<Build Include="Views\xxxxxxxxx.sql" />
<Build Include="Procedures\xxxxxxxxx.sql" />
<Build Include="Procedures\xxxxxxxxx.sql" />
</ItemGroup>
<ItemGroup>
<RefactorLog Include="xxxxxxxxx.refactorlog" />
</ItemGroup>
<ItemGroup>
<PostDeploy Include="Script.PostDeployment1.sql" />
</ItemGroup>
<ItemGroup>
<PreDeploy Include="Script.PreDeployment1.sql" />
</ItemGroup>
<ItemGroup>
<None Include="xxxxxxxxx.publish.xml" />
</ItemGroup>
</Project>
That 'xxx' parts is just anonymization of course.
I was able to successfully build this project and get a DACPAC file by simple
cd solutionroot
dotnet build MyProject.sln
or
cd solutionroot/dbproject
dotnet build MyProject.sqlproj
I did not have to add /p:NetCoreBuild=true - the tooling in 0.1.10 detects that it is ran from dotnet command and sets this automatically.
This worked both when the build was done at my local dev machine (Win10, VS2022, dotnet --version = 7.0.302), and when ran on CI agent (Amazon Linux 2).
The CI system was AWS CodeBuild and script used was:
version: 0.2
phases:
install:
runtime-versions:
dotnet: latest
pre_build:
commands:
- dotnet --version
- dotnet restore
build:
commands:
- dotnet build -c Release
Not much happening really. The relevant output parts were:
[Container] 2023/07/08 20:36:06 Running command dotnet --version
6.0.408
(...snip...)
xxxx.Database -> /codebuild/output/src3703237027/src/git-codecommit.eu-west-3.amazonaws.com/v1/repos/xxxx/xxxx/xxxx/bin/Release/xxxx.dll
xxxx.Database -> /codebuild/output/src3703237027/src/git-codecommit.eu-west-3.amazonaws.com/v1/repos/xxxx/xxxx/xxxx/bin/Release/xxxx.dacpac
So it actually took 6.0, different than on my local. A bit odd, but it still built the dacpac.
Two things worth noting - while dotnet build is able to build a DACPAC, the tooling is "preview" and if I remember correcly, the docs at GitHub mention that not all features are supported. I have no idea how the feature gaps compare to the gaps in rr-wfm's nuget, but the tool is still developed, so there's hope :)
The second thing to note is that while dotnet build is able to build the DACPAC, the dotnet publish is NOT able to run it. Trying to
dotnet publish -c Release -p:SqlPublishProfilePath=./xxxx.publish.xml
at best currently ends up with following error
/root/.nuget/packages/microsoft.build.sql/0.1.10-preview/tools/netstandard2.1/Microsoft.Data.Tools.Schema.SqlTasks.targets(1869,7): error MSB4018: The "SqlPublishTask" task failed unexpectedly.
/root/.nuget/packages/microsoft.build.sql/0.1.10-preview/tools/netstandard2.1/Microsoft.Data.Tools.Schema.SqlTasks.targets(1869,7): error MSB4018: System.PlatformNotSupportedException: Microsoft.Data.SqlClient is not supported on this platform.
/root/.nuget/packages/microsoft.build.sql/0.1.10-preview/tools/netstandard2.1/Microsoft.Data.Tools.Schema.SqlTasks.targets(1869,7): error MSB4018: at Microsoft.Data.SqlClient.SqlConnectionStringBuilder..ctor(String connectionString)
/root/.nuget/packages/microsoft.build.sql/0.1.10-preview/tools/netstandard2.1/Microsoft.Data.Tools.Schema.SqlTasks.targets(1869,7): error MSB4018: at Microsoft.Data.Tools.Schema.Common.SqlClient.SqlConnectionFactory..ctor(String connectionString)
/root/.nuget/packages/microsoft.build.sql/0.1.10-preview/tools/netstandard2.1/Microsoft.Data.Tools.Schema.SqlTasks.targets(1869,7): error MSB4018: at Microsoft.Data.Tools.Schema.Sql.Deployment.CachedRegistryConnectionString..ctor(String connectionString, Boolean loadFromRegistry)
I get this error BOTH when I try to run the dotnet publish on my local dev machine (where everything worked), and obviously also when I try that at the CI agent.
This is because the dotnet publish command isn't currently supported by the 0.1.10 tooling. It probably will not be supported at all, for some probably important, probably internal reason.
Instead of dotnet publish, a separate tool is provided by DacFx, called SqlPackage. It's advertised as the cross-platform tool for running DACPACs.
It can be installed with dotnet:
dotnet tool install -g microsoft.sqlpackage
and it can publish the DACPAC, although with a bit different commandline syntax (see the previous link above for details):
SqlPackage /Action:Publish /SourceFile:".dacpac" /Profile:"....."
I have not tried installing and running this SqlPackage tool, but looks very promising.
