Create a dacpac for an Azure Synapse Analytics dedicated SQL Pool using Azure Data Studio

Reading Time: 5 minutes

In this post I want to cover how you can create a dacpac for an Azure Synapse Analytics dedicated SQL Pool using Azure Data Studio.

Because somebody asked if it’s possible to create a dacpac for a SQL Pool a while back. Using the #SQLHelp hashtag on Twitter. In fact, it was asked whilst I was doing the MVP Challenge and now is my first chance to post about it.

Many ways to create a dacpac for an Azure Synapse SQL Pool

In reality, you can create a dacpac for a database that’s inside an Azure Synapse Analytics dedicated SQL Pool using a lot of the methods that you use to create them for SQL Server databases.

Azure Data Studio can be an appealing alternative SQL Server Data Tools (SSDT) for tasks like this. Due to various reasons. For instance, it’s a multi-platform solution that is easy to install.

With this in mind, I decided in this post to cover how to create a dacpac for an Azure Synapse Analytics dedicated SQL Pool using Azure Data Studio.

Azure Data Studio example

In the below example Azure Data Studio is connected to an Azure Synapse Analytics dedicated SQL Pool. Which contains a simple database.

Create database project in Azure Data Studio

Before you can create a dacpac in Azure Data Studio you must create a Database Project.

If you have the SQL Database Projects extension installed in Azure Data Studio you can create a project with ease. You can create a database project by right clicking the database and selecting ‘Create Project From database’.

When you go to create the project in Azure Data Studio you enter a name and location. Note that a new subfolder will be created in the location you specify based on the name of the project.

If you try to enter spaces in the ‘Name’ box above it does not work at first. One workaround for this is that you can type the name without spaces first and then select a folder location. Once a folder location is entered, you can then go back to the name section to add spaces. I found this out whilst experimenting with the options.

After you click the ‘Create’ button in the above screen the project is created. You can see how it looks below.

Change target Platform in Azure Data Studio

Before you create a dacpac, you must first change the target platform so that you can deploy to a dedicated SQL Pool without any fuss. You can do this by right clicking the project and selecting ‘Manage’.

From there select ‘Change Target Platform’.

You can then select ‘Microsoft Azure Synapse Dedicated SQL Pool’.

Note that if you are using an older version of Azure Data Studio you must select Microsoft Azure SQL Data Warehouse instead. Because that is the older option to select to create a dacpac for a dedicated SQL Pool.

Azure Synapse SQL Pool GitHub repository

You can see an example of an Azure Pipelines YAML file for this in a GitHub repository that I have made public. Which is called AzureDevOps-AzureSynapseSQLPool.

This repository also contains a Wiki with material in it to help you.

Create the dacpac in Azure Data Studio

To create the dacpac within Azure Data Studio afterwards you can right click the database project and click on ‘Build’.

By default, this will create the dacpac and supplementary files in the ‘bin\debug’ subfolder in the project location.

Create dacpac in deployment pipeline

Another option to create a dacpac is to use the database project as the source of a deployment pipeline.

To do this, you must make the folder where your database project is saved to into a Git repository.

You can look to do this in Azure Data Studio itself. However, there’s a lot of material on different ways you can do this online as well. One easy way is to use the Git GUI if you have downloaded Git locally.

Once you have done this, you can synchronize your database project with a remote Git repository that supports CI/CD pipelines. For example, in Azure DevOps or GitHub. Once you have done that you can look to create the dacpac in a deployment pipeline instead.

You can see an example of how to do this for Azure SQL Database in my post about deploying to Azure SQL Database using GitHub Actions.

One difference is that you can use a database project based on a dedicated SQL Pool instead of an Azure SQL Database. Another is that you would enter the connection string for your dedicated SQL Pool. Apart from that, the logic is the same.

Final word

I hope this guide on how to create a dacpac for an Azure Synapse Analytics dedicated SQL Pool using Azure Data Studio proves to be useful. Especially for the person who originally asked about creating dacpacs for dedicated SQL Pools.

Let me know if this post is useful with a comment. As always, feel free to reach out to me if you have any questions.