Build error SQL71501: [….] contains an unresolved reference to an object. Either the object does not exist or the reference is ambiguous

[elamathib]

• SqlPackage or DacFx Version: Microsoft.Build.Sql 1.0.0
• .NET Framework (Windows-only) or .NET Core:9.0.102
• Environment (local platform and source/target platforms): Microsoft Azure Synapse

Steps to Reproduce:

1. Create a function with self join on a system view like INFORMATION_SCHEMA
2. Perform dotnet build

Did this occur in prior versions? If not - which version(s) did it work in? no this is our first time onboarding to DACPAC

(DacFx/SqlPackage/SSMS/Azure Data Studio) DacFx

Build error SQL71501: [….] contains an unresolved reference to an object. Either the object does not exist or the reference is ambiguous

[dzsquared]

Great question.
When you want to reference system objects you need to add a database reference to the "master dacpac" for the platform you're developing on. Since you're using the Microsoft.Build.Sql SDK, you can jump right to using a package reference. 🥳

If I understand correctly you're developing for Synapse dedicated pools, so the master dacpac package is published here: https://www.nuget.org/packages/Microsoft.SqlServer.Dacpacs.Synapse.Master

There's a couple ways to add this to your project (via the IDE, CLI, etc).

You can add it to the project in terminal with:

dotnet add package Microsoft.SqlServer.Dacpacs.Synapse.Master --version 130.2.5

Or you can directly edit your project file such that it contains the <PackageReference> element (probably end up looking something like this):

<?xml version="1.0" encoding="utf-8"?>
<Project DefaultTargets="Build">
  <Sdk Name="Microsoft.Build.Sql" Version="1.0.0" />
  <PropertyGroup>
    <Name>SynapseWithRef</Name>
    <DSP>Microsoft.Data.Tools.Schema.Sql.SqlDwDatabaseSchemaProvider</DSP>
    <ModelCollation>1033, CI</ModelCollation>
  </PropertyGroup>
  <ItemGroup>
    <PackageReference Include="Microsoft.SqlServer.Dacpacs.Synapse.Master" Version="130.2.5" />
  </ItemGroup>
</Project>

At the moment the remainder of the system dacpacs are listed at the root of the repo, in case you need another one:

Docs on package references: https://learn.microsoft.com/en-us/sql/tools/sql-database-projects/concepts/package-references?view=sql-server-ver16&pivots=sq1-command-line