Forum Discussion

CloudVasu's avatar
CloudVasu
Helper I
4 months ago
Solved

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

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 Sq...
CICD
  • Aparnaa_MS's avatar
    Aparnaa_MS
    4 months ago

    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
Aparnaa_MS's avatar
Aparnaa_MS
Advocate III
4 months ago

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