SqlPublish fails when run from dotnet

[derkyjadex]

• SqlPackage or DacFx Version: Microsoft.Build.Sql 0.2.0-preview
• .NET Framework (Windows-only) or .NET Core: 8.0.400
• Environment (local platform and source/target platforms): Windows, Linux

Steps to Reproduce:

1. Create new directory with two files, test.sqlproj and test.publish.xml:

<?xml version="1.0" encoding="utf-8"?>
<Project DefaultTargets="Build">
  <Sdk Name="Microsoft.Build.Sql" Version="0.2.0-preview" />
  <PropertyGroup>
    <Name>test</Name>
    <DSP>Microsoft.Data.Tools.Schema.Sql.Sql150DatabaseSchemaProvider</DSP>
    <ModelCollation>1033, CI</ModelCollation>
  </PropertyGroup>
</Project>

<?xml version="1.0" encoding="utf-8"?>
<Project ToolsVersion="12.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
  <PropertyGroup>
    <TargetDatabaseName>test</TargetDatabaseName>
    <DeployScriptFileName>test.sql</DeployScriptFileName>
    <ProfileVersionNumber>1</ProfileVersionNumber>
    <TargetConnectionString>Data Source=localhost;Integrated Security=True</TargetConnectionString>
  </PropertyGroup>
</Project>

2. Build the project with dotnet build
3. Publish the database with dotnet msbuild -t:SqlPublish -p:SqlPublishProfilePath=test.publish.xml

Expected outcome

The database should be published to the configured server.

Actual outcome

The command fails with the following output:

MSBuild version 17.11.3+0c8610977 for .NET
C:\Users\XXXXX\.nuget\packages\microsoft.build.sql\0.2.0-preview\tools\netstandard2.1\Microsoft.Data.Tools.Schema.SqlTasks.targets(1887,7): warning : Exception has been thrown by the target of an invocation. [C:\Users\XXXXX\SqlTest\test.sqlproj]
C:\Users\XXXXX\.nuget\packages\microsoft.build.sql\0.2.0-preview\tools\netstandard2.1\Microsoft.Data.Tools.Schema.SqlTasks.targets(1887,7): error MSB4181: The "SqlPublishTask" task returned false but did not log an error. [C:\Users\XXXXX\SqlTest\test.sqlproj]

Running in Linux gives the same results.

Did this occur in prior versions? If not - which version(s) did it work in?

Same results from 0.1.19-preview and 0.1.15-preview. Not tested with earlier versions.

Workarounds

This does work as expected when directly calling the MSBuild installed with Visual Studio (ie. exactly the same command but without the dotnet prefix). Obviously that is not available outside Windows and is a much bigger pre-requisite even then.

The Publish target fails with a different error, as reported in https://github.com/microsoft/DacFx/issues/447. With the VS MSBuild, both the SqlPublish and Publish targets work as expected.

[ErikEJ]

That is a very old SDK version..

[derkyjadex]

That is a very old SDK version..

Not sure how the versioning works for the build SDK, but that's the latest available on nuget.org and is only 2 months old.

[ErikEJ]

???

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

[dzsquared]

The SDK version is correct - we are on 0.2.0-preview

dotnet publish in the Microsoft.Build.Sql SDK is not being implemented to run Sqlpackage/dacfx publish, we plan to converge on the csproj publish behavior (#447)

The /Profile: parameter on sqlpackage publish should assist here: https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-publish?view=sql-server-ver16

dotnet build
sqlpackage /a:publish /sourcefile:bin/Debug/yourproject.dacpac /profile:test.publish.xml

if you don't have sqlpackage:

dotnet tool install -g microsoft.sqlpackage