Skip to content

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

Reading Time: 4 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.

Dedicated SQL Pool in Azure Data Studio
Database in a dedicated SQL Pool

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’.

Create Project From Database context menu

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

Create Project from database screen
Create Project from database screen

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.

Database Project in Azure Data Studio
Database Project in ADS

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’.

Selecting Manage in Azure Data Studio
Selecting Manage on the context menu

From there select ‘Change Target Platform’.

Change Target Platform in Azure Data Studio
Change Target Platform

You can then select ‘Microsoft Azure SQL Data Warehouse’. Since that is the target platform you currently have to select to deploy to a dedicated SQL Pool.

Select Microsoft Azure SQL Data Warehouse
Select Microsoft Azure SQL Data Warehouse

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’.

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

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

Created dacpac for an Azure Synapse Analytics Dedicated SQL Pool
Created dacpac in folder

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 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.

Published inAzure Data EngineeringAzure Synapse AnalyticsVersion Control

5 Comments

  1. madhusudhan madhusudhan

    Pipeline fails with below error

    Done Building Project “D:\a\1\s\a3sdacpac.sqlproj” (default targets) — FAILED

    Done Building Project “D:\a\1\s\a3sdacpac.sln” (default targets) — FAILED.

    • Kevin Chant Kevin Chant

      Are you trying to create a dacpac in Azure DevOps or GitHub Actions?

Leave a Reply

Your email address will not be published. Required fields are marked *