dotnet build with external dacpac reference

[wessonben]

We are trying to build a sqlproj that uses a reference to an external database, via SqlCmdVariables and including a copy of the external dacpac. This is a sample project file...

<Project Sdk="MSBuild.Sdk.SqlProj/1.10.0">
    <PropertyGroup>
        <SqlServerVersion>Sql140</SqlServerVersion>
        <TargetFramework>netstandard2.1</TargetFramework>
	    <BundledNETCoreAppTargetFrameworkVersion>5.0</BundledNETCoreAppTargetFrameworkVersion>
    </PropertyGroup>
	<ItemGroup>
        <Content Remove=".\dbo\Scripts\**\*.sql" />
    </ItemGroup>
	<ItemGroup>
	    <PreDeploy Include=".\dbo\Scripts\PreDeployment.sql" />
		<PostDeploy Include=".\dbo\Scripts\PostDeployment.sql" />
    </ItemGroup>
	<ItemGroup>
		<ArtifactReference Include="DBReferences\ExternalDatabase.dacpac">
			<HintPath>DBReferences\ExternalDatabase.dacpac</HintPath>
			<SuppressMissingDependenciesErrors>True</SuppressMissingDependenciesErrors>
			<DatabaseSqlCmdVariable>ExternalDatabaseName</DatabaseSqlCmdVariable>
		</ArtifactReference>
	</ItemGroup>
	<ItemGroup>
		<SqlCmdVariable Include="ExternalDatabaseName">
			<DefaultValue>ExternalDatabaseName</DefaultValue>
			<Value>$(SqlCmdVar__1)</Value>
		</SqlCmdVariable>
	</ItemGroup>
</Project>

The issue seems to be that whilst it builds successfully within Visual Studio using the traditional .Net Framework tooling (and project file), it fails to build using dotnet build and the above project file. We are really not sure if this is supposed to work, or if we have made a mistake, but ideally we should be able to build a new dacpac that includes artifacts from an external one. The error output is as follows...

Microsoft (R) Build Engine version 17.0.0+c9eb9dd64 for .NET
Copyright (C) Microsoft Corporation. All rights reserved.

  Determining projects to restore...
  All projects are up-to-date for restore.
  Using package name WorldRugby.UnionService.Data.Database.Build and version 1.0.0
  Using SQL Server version Sql140
  Adding SqlCmd variable ExternalDatabaseName
  Adding C:\..some folders..\PostDeployment.sql to the model
  Unhandled exception: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation.
   ---> Microsoft.SqlServer.Dac.Model.DacModelException: Add or update objects failed due to the following errors:
EXEC : error SQL46010: Incorrect syntax near :. [C:\..some folders..\Database.Build.sqlproj]
     at Microsoft.SqlServer.Dac.Model.SqlSchemaModelObjectService.ThrowIfModelErrorsExist(String errorMessage)
     at Microsoft.SqlServer.Dac.Model.SqlSchemaModelObjectService.DoAddOrUpdateObjects(String inputScript, String sourceName, TSqlObjectOptions options)
     at Microsoft.SqlServer.Dac.Model.SqlSchemaModelObjectService.AddOrUpdateObjects(String inputScript, String sourceName, TSqlObjectOptions options)
     at Microsoft.SqlServer.Dac.Model.TSqlModel.AddOrUpdateObjects(String inputScript, String sourceName, TSqlObjectOptions options)
     at MSBuild.Sdk.SqlProj.DacpacTool.PackageBuilder.AddInputFile(FileInfo inputFile) in /home/runner/work/MSBuild.Sdk.SqlProj/MSBuild.Sdk.SqlProj/src/DacpacTool/PackageBuilder.cs:line 78
     at MSBuild.Sdk.SqlProj.DacpacTool.Program.BuildDacpac(BuildOptions options) in /home/runner/work/MSBuild.Sdk.SqlProj/MSBuild.Sdk.SqlProj/src/DacpacTool/Program.cs:line 121
     --- End of inner exception stack trace ---
     at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor, Boolean wrapExceptions)
     at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
     at System.Delegate.DynamicInvokeImpl(Object[] args)
     at System.Delegate.DynamicInvoke(Object[] args)
     at System.CommandLine.Invocation.ModelBindingCommandHandler.InvokeAsync(InvocationContext context)
     at System.CommandLine.Invocation.InvocationPipeline.<>c__DisplayClass4_0.<<BuildInvocationChain>b__0>d.MoveNext()
  --- End of stack trace from previous location ---
     at System.CommandLine.Builder.CommandLineBuilderExtensions.<>c.<<UseParseErrorReporting>b__21_0>d.MoveNext()
  --- End of stack trace from previous location ---
     at System.CommandLine.Builder.CommandLineBuilderExtensions.<>c__DisplayClass16_0.<<UseHelp>b__0>d.MoveNext()
  --- End of stack trace from previous location ---
     at System.CommandLine.Builder.CommandLineBuilderExtensions.<>c__DisplayClass25_0.<<UseVersionOption>b__0>d.MoveNext()
  --- End of stack trace from previous location ---
     at System.CommandLine.Builder.CommandLineBuilderExtensions.<>c__DisplayClass23_0.<<UseTypoCorrections>b__0>d.MoveNext()
  --- End of stack trace from previous location ---
     at System.CommandLine.Builder.CommandLineBuilderExtensions.<>c.<<UseSuggestDirective>b__22_0>d.MoveNext()
  --- End of stack trace from previous location ---
     at System.CommandLine.Builder.CommandLineBuilderExtensions.<>c.<<UseParseDirective>b__20_0>d.MoveNext()
  --- End of stack trace from previous location ---
     at System.CommandLine.Builder.CommandLineBuilderExtensions.<>c.<<UseDebugDirective>b__11_0>d.MoveNext()
  --- End of stack trace from previous location ---
     at System.CommandLine.Builder.CommandLineBuilderExtensions.<>c.<<RegisterWithDotnetSuggest>b__10_0>d.MoveNext()
  --- End of stack trace from previous location ---
     at System.CommandLine.Builder.CommandLineBuilderExtensions.<>c__DisplayClass14_0.<<UseExceptionHandler>b__0>d.MoveNext()

[jmezach]

@wessonben Thanks for reporting this issue. Based on the output you've provided it seems that the PostDeployment.sql script is being added to the model, which doesn't work because it probably contains statements that cannot be used in that context. It can be used as a pre/post-deployment script however. Based on the project file though, it looks like you've already set that up correctly, except if there's some other PostDeployment.sql project somewhere in your directory structure.

That being said, we don't currently support ArtifactReference directly. Instead you should build a NuGet package from DBReferences\ExternalDatabase.dacpac and then use PackageReference to reference it in your project. We have some documentation on how to set this up in our README.