CI/CD DACPAC Build Fails (SQL71561) When Views Reference Tables in Another Fabric Lakehouse

[CloudVasu]

Hi everyone,

I’m building a CI/CD pipeline for Microsoft Fabric Data Warehouse using Azure DevOps. My project is a SQL Server Database Project (.sqlproj) using the Microsoft.Build.Sql SDK and the SqlDwUnifiedDatabaseSchemaProvider.

Problem:
Some views in my project reference tables in another lakehouse, for example:

CREATE VIEW [views].[my_view] AS
SELECT * FROM [AnotherLakehouse].[dbo].[SomeTable]

When building the DACPAC, I get this error:

SQL71561: View: [sourceviews].[my_view] has an unresolved reference

to object [AnotherLakehouse].[dbo].[SomeTable]

What I’ve tried:

<SuppressTSqlWarnings>71501;71561</SuppressTSqlWarnings> — doesn’t work, still raises as a hard error.

<SuppressMissingDependenciesErrors>true</SuppressMissingDependenciesErrors> — partially works, but doesn’t suppress all errors.

SqlCmdVariables — sqlpackage doesn’t substitute variables inside the CREATE VIEW body DDL at deploy time.

Has anyone found a reliable way to handle DACPAC references to tables in another lakehouse? Any guidance or best practices would be appreciated.

[CloudVasu]

@Aparnaa_MS  Thank you for the detailed response. this approach makes sense.

I’d like to explore the DACPAC-based ArtifactReference further. Could you please clarify the following:

What’s the recommended way to create the reference DACPAC,should it be generated from the Lakehouse schema or maintained as a minimal stub?

How should the ArtifactReference be configured in the .sqlproj?

Does this approach fully support SDK-style projects using Microsoft.Build.Sql, or are there any limitations?

Just to confirm, this is used only for build-time resolution and does not deploy the referenced Lakehouse objects?

If possible, could you share a sample .sqlproj configuration?

Thanks again for your guidance.

[Aparnaa_MS]

Hi @CloudVasu 

Happy to clarify,

 

Question 1: Best practice is to generate the reference DACPAC from the actual Lakehouse schema whenever possible but if you only need to include the tables/views your warehouse project actually references a targeted stub works too, as long as the referenced objects are present in the model.

Question 2: You can add the following snippet to your warehouse.sqlproj

<ItemGroup>

  <ArtifactReference Include="..\LakehouseProject\bin\Debug\AnotherLakehouse.dacpac">

    <HintPath>..\LakehouseProject\bin\Debug\AnotherLakehouse.dacpac</HintPath>

    <DatabaseVariableLiteralValue>AnotherLakehouse</DatabaseVariableLiteralValue>

    <SuppressMissingDependenciesErrors>False</SuppressMissingDependenciesErrors>

  </ArtifactReference>

</ItemGroup>

 

Two points to note 

• Use DatabaseVariableLiteralValue — not DatabaseSqlCmdVariable 
  Because your views use literal 3-part names, the reference needs to match that exact database name during build. SQLCMD variables are generally intended for deployment-time substitution, so they are not the best fit for this specific build-time workaround. In this case, using DatabaseVariableLiteralValue is a simpler and more reliable way to match the literal database name used in the T-SQL
• Keep SuppressMissingDependenciesErrors as False as once the reference is correctly set up, you do not need suppression.

Question 3: It works with SDK-style projects using Microsoft.Build.Sql, but with caveats.

Direct .dacpac artifact references can be used, however Microsoft Learn recommends project references or package references for new SDK-style development rather than direct .dacpac artifact references. 

Question 4: Yes,the Lakehouse DACPAC, build-time only, serves purely as a schema reference to satisfy the model during the Warehouse project build. It does nothing at deployment time.

I don’t currently have a full end-to-end sample project to share, but the snippet above shows the core .sqlproj configuration required for this setup.
Hope this answered some of your questions!

Thanks

Aparnaa M S

[v-aatheeque]

Hi @CloudVasu 

Have you had a chance to look through the responses shared earlier? If anything is still unclear, we’ll be happy to provide additional support.

[Aparnaa_MS]

Hi @CloudVasu 

The Microsoft.Build.Sql SDK with SqlDwUnifiedDatabaseSchemaProvider performs full dependency resolution at compile time. When your view references AnotherLakehouse.dbo.SomeTable, the DACPAC build engine cannot resolve that external database unless it has an explicit schema model for it.

It does not assume the object will exist at runtime.

According to an article published, built CI/CD support for Warehouses, but didn't fully extend it to cover Lakehouse to Warehouse cross-references yet. Until then, you might have to use a workaround.

After researching, the only reliable solution that could be found is to use a DACPAC-based ArtifactReference to resolve cross-Lakehouse build errors.

Basically the fix is to provide the build engine with a resolvable schema model for the Lakehouse, in the form of a DACPAC file used purely as a reference artifact.

Let me know if you want to know more.

Thanks