Continuous integration and deployment for Azure SQL Data Warehouse

This simple tutorial outlines how to integrate your SQL Server Data tools (SSDT) database project with Azure DevOps and leverage Azure Pipelines to set up continuous integration and deployment. This tutorial is the second step in building your continuous integration and deployment pipeline with SQL Data Warehouse.

Before you begin

  • Go through the source control integration tutorial

  • Create a self-hosted agent that has the SSDT Preview Bits (16.3 preview 2 and higher) installed for SQL Data Warehouse (preview)

  • Set up and connect to Azure DevOps

    Note

    SSDT is currently in preview where you will need to leverage a self-hosted agent. The Microsoft-hosted agents will be updated in the next few months.

Continuous integration with Visual Studio build

  1. Navigate to Azure Pipelines and create a new build pipeline

    New Pipeline

  2. Select your source code repository (Azure Repos Git) and select the .NET Desktop app template

    Pipeline Setup

  3. Edit your YAML file to use the proper pool of your agent. Your YAML file should look something like this:

    YAML

At this point, you have a simple environment where any check-in to your source control repository master branch should automatically trigger a successful Visual Studio build of your database project. Validate the automation is working end to end by making a change in your local database project and checking in that change to your master branch.

Continuous deployment with the Azure SQL Database deployment task

  1. Add a new task using the Azure SQL Database deployment task and fill in the required fields to connect to your target data warehouse. When this task runs, the DACPAC generated from the previous build process is deployed to the target data warehouse.

    Deployment Task

  2. When using a self-hosted agent, make sure you set your environment variable to use the correct SqlPackage.exe for SQL Data Warehouse. The path should look something like this:

    Environment Variable

    C:\Program Files (x86)\Microsoft Visual Studio\2019\Preview\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\150

    Run and validate your pipeline. You can make changes locally and check in changes to your source control that should generate an automatic build and deployment.

Next steps