Forum Discussion

evrise's avatar
evrise
Icon for Advocate II rankAdvocate II
2 years ago
Solved

How to Build Warehouse DB Project Locally or within CI Pipeline Using MSSQL Database Projects

Hi,

I am currently working on a database project in Azure Data Studio using MSSQL Database Projects. My goal is to build the project locally. The project involves creating views that reference tables created using Spark and stored in a Microsoft Fabric Lakehouse.

Here is the specific challenge I am facing:

DB Project Build: How can I configure and build my database project, ensuring that it references the tables in the Fabric Lakehouse?

 

I would greatly appreciate detailed steps, configurations, and any relevant scripts or examples that could help in setting up both local builds and CI pipeline builds for this scenario.

 

Thank you for your assistance!

data warehouse
warehouse
  • AndyDDC's avatar
    AndyDDC
    2 years ago

    Hi evrise in order to build your Warehouse project locally that has a view that references a Lakehouse table, you'll need to create another database project locally for the Lakehouse database and then create a Database Reference.

     

    The issue at the moment is that the only database reference method that seems to work is dacpac, so once you have created the Lakehouse database project, you'll then need to successfully build it to generate a dacpac, then you can create a database reference in the Warehouse project that points to the lakehouse dacpac file.  

     

    Please note you can't actually do anything with the Lakehouse dacpac file (like deploy to lakehouse as it's not supported) other than use it as a database reference.

     

    I hope that helps.

     

     

     

    -----------------------------------------------------------------------------

    If my reply has been useful, please consider providing Kudos and

    marking it as the solution.

    -----------------------------------------------------------------------------

     

7 Replies

  • AndyDDC's avatar
    AndyDDC
    Icon for Most Valuable Professional rankMost Valuable Professional
    2 years ago

    Hi evrise in order to build your Warehouse project locally that has a view that references a Lakehouse table, you'll need to create another database project locally for the Lakehouse database and then create a Database Reference.

     

    The issue at the moment is that the only database reference method that seems to work is dacpac, so once you have created the Lakehouse database project, you'll then need to successfully build it to generate a dacpac, then you can create a database reference in the Warehouse project that points to the lakehouse dacpac file.  

     

    Please note you can't actually do anything with the Lakehouse dacpac file (like deploy to lakehouse as it's not supported) other than use it as a database reference.

     

    I hope that helps.

     

     

     

    -----------------------------------------------------------------------------

    If my reply has been useful, please consider providing Kudos and

    marking it as the solution.

    -----------------------------------------------------------------------------

     

    • evrise's avatar
      evrise
      Icon for Advocate II rankAdvocate II
      2 years ago

      Thanks a lot, AndyDDC. Excuse me for the additional question. How do you handle changes in the referenced Lakehouse DACPAC? As you mentioned, the only way to create tables is through Spark, not DB Project. So, when the Lakehouse is changed, do you always need to follow those steps and rebuild the DACPAC for the Lakehouse?

      Do you have it set up in your CI pipeline so that a new DACPAC for the Lakehouse is always created there? Or do you have a method to build the DACPAC once and then reuse it?

      Thanks

      • AndyDDC's avatar
        AndyDDC
        Icon for Most Valuable Professional rankMost Valuable Professional
        2 years ago

        In terms of keeping the Lakehouse dacpac updated, yes you'll need to do a diff in Azure Data Studio to get the latest changes and sync with the local db project, and build to generate the latest dacpac.

         

        I haven't tried this myself, but I would imagine the CI process (eg in Azure DevOps) would simply build and output the dacpac for the latest version on the lakehouse db project.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi evrise ,

     

    Your requirement is to understand how to connect Fabric warehouse from visual studio and save as a Database Project, right?

     

    First, create a Warehouse and then create a view.

     

    Click on the Settings icon in the warehouse.

     

    Navigate to the About section and find the SQL connection string at the bottom of the page.

     

    Open Azure Data Studio and create a new connection.

     

    Select "Microsoft SQL Server" as the connection type.

     

    Enter the connection string copied from Fabric Data Warehouse.

     

    Select "Microsoft Entra ID" as the authentication type and complete the authentication.

     

    Select your warehouse and view.

     

    In Azure Data Studio, install the "SQL Database Projects" extension.

     

    Create a new SQL database project.

     

    Add the Fabric Data Warehouse you just imported and you're ready to go.

     

    Finally, remember to save the project.

     

    If you have any other questions please feel free to contact me.

     

    Best Regards,
    Yang
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!