Azure Pipelines - Generating DB Script with SqlPackage.exe

Recently I needed to automate the generation of a database script deploy from a SQL Server Database Project on our Azure Pipelines continuous integration. Below I will describe the steps to achieve such thing.

Building the solution

A SQL Server Database Project has a solution (.sln) as any other kind of Visual Studio projects and we need to build it to generate our DACPAC.

A data-tier application (DAC) is a logical database management entity that defines all of the SQL Server objects - like tables, views, and instance objects, including logins - associated with a user’s database. A DAC is a self-contained unit of SQL Server database deployment that enables data-tier developers and database administrators to package SQL Server objects into a portable artifact called a DAC package, also known as a DACPAC

The variables used in the script portions are described in the Complete azure-pipelines.yml in the end of this post

Generating the .sql script

With our .dacpac file built, now we need to generate our .sql database script. To perform this operation we need the sqlpackage.exe, this command-line is available in some Azure Pipelines images, as vs2017-win2016, so you need to use it on your azure-pipelines.yml file:

Add the CmdLine task below to generate the .sql database script:

Publishing the .sql script

Now we need to publish the .sql file to the build artifacts:

Complete azure-pipelines.yml

Extra points

In the approach described above the .sql files is not automatically applied to the database, so someone need to download, check and apply the file manually against the target database. If you want to apply the database script automatically you can change CmdLine task that generate the .sql file to update the target database using the action:publish option of sqlpackage.exe.