Continuous integration and deployment for dedicated SQL pool in Azure Synapse Analytics
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 for data warehousing.
Before you begin
Go through the source control integration tutorial
Set up and connect to Azure DevOps
Continuous integration with Visual Studio build
Navigate to Azure Pipelines and create a new build pipeline.
Select your source code repository (Azure Repos Git) and select the .NET Desktop app template.
Edit your YAML file to use the proper pool of your agent. Your YAML file should look something like this:
At this point, you have a simple environment where any check-in to your source control repository main 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 main branch.
Continuous deployment with the Azure Synapse Analytics (or Database) deployment task
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. You can also use the Azure Synapse Analytics deployment task.
If you are using a self-hosted agent, make sure you set your environment variable to use the correct SqlPackage.exe for Azure Synapse Analytics. The path should look something like this:
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.