Build a VS SSDT Project on an Ubuntu GitHub Actions Runner

Ollie Flavel
March 2, 2024

13 minutes to read

In this blog post, we will explore how to build a Visual Studio SQL Server Data Tools (SSDT) project on an Ubuntu GitHub Actions runner. This is particularly useful for developers who want to leverage the power of CI/CD in their database development workflow. We will discuss two options: using the new SDK-style project and converting your existing Visual Studio SSDT project to support the VS Code SQL Database Projects Extension.

Introduction
Prerequisites
Creating and Building an SDK-style SQL Project
Converting an Existing SSDT Project to Support VS Code Database Project Extension (Non-SDK-style SQL Project)
Setting up GitHub Actions to Build a SQL Database Project
Conclusion

Introduction

These days Linux has become the defacto operating system for building and deploying your application into the cloud, delivering advantages such as faster builds and reduced licensing costs. With the introduction of .NET Core by Microsoft, we now have a cross platform framework that can be built on any of the major operating systems (Windows, macOS & Linux), breaking the dependency on Windows! But until recently there was still one piece of the stack tied to Windows, the Visual Studio Database Project! Follow on below to explore the options we have for working with SQL Database Projects cross platform.

Prerequisites

Before we begin, ensure you have the following:

A GitHub account
An existing SQL Server Database Project
VS Code installed on your machine
SQL Database Projects Extension installed
.NET Core SDK installed

Creating and Building an SDK-style SQL Project

Microsoft has introduced a new SDK Microsoft.Build.Sql for building SQL Database Projects (.sqlproj) in .NET Core and .NET 5+. At the time of writing this blog post, the SDK is still in preview with the latest preview version being 0.1.15-preview. Once this SDK becomes generally available (GA) it will be the preferred method for building SQL Database Projects, simplifying the build process to generate your deployable .dacpac file. The new SDK-style projects are compatible with VS Code SQL Database Projects and Azure Data Studio, but in my testing of the preview I hit some issues loading the SDK-style projects in Visual Studio.

You have three options to create/build an SDK-style SQL Project, the .NET CLI, VS Code SQL Database Project Extensions and Azure Data Studio, we will explore the first option the .NET CLI.

From your command line of choice install the Microsoft.Build.Sql.Templates templates:

dotnet new -i Microsoft.Build.Sql.Templates

Now create a new SDK-style SQL Project using the sqlproj template:

dotnet new sqlproj -n EmployeeDb

Add a new table dbo.Employee in a .sql file alongside the project file:

CREATE TABLE [dbo].[Employee]
(
    [Id] INT NOT NULL PRIMARY KEY,
    [FirstName] NVARCHAR(200) NOT NULL,
    [LastName] NVARCHAR(200) NOT NULL,
    [Email] NVARCHAR(200) NOT NULL
);

Finally build the project to create a .dacpac file:

dotnet build /p:NetCoreBuild=true

You should now have a .dacpac file that you can use to deploy your SQL database. You can find more information on the Microsoft Learn website Use SDK-style SQL projects with the SQL Database Projects extension (Preview) and you can find the project on GitHub microsoft/DacFx.

Converting an Existing SSDT Project to Support VS Code Database Project Extension (Non-SDK-style SQL Project)

If you have an existing SSDT project created with Visual Studio that you want to convert to support the VS Code Database Project Extension (or Azure Data Studio), then it should be as easy as opening the existing project in the VS Code Database Projects tab:
VS Code - Open existing database project

When prompted click Yes to update the project:
VS Code - Prompt to update database project

And you now have an updated .sqlproj file as well as a backup of the original file: VS Code - Updated database project

Now if you didn’t get the prompt to update or you prefer to do things yourself, you can make the required changes manually, start by opening the .sqlproj file in VS Code: VS Code - Open unmodified .sqlproj file

First you need to update the imports, find the <Import /> tags and modify them like below:

- <Import Condition="'$(SQLDBExtensionsRefPath)' != ''" Project="$(SQLDBExtensionsRefPath)\Microsoft.Data.Tools.Schema.SqlTasks.targets" />
- <Import Condition="'$(SQLDBExtensionsRefPath)' == ''" Project="$(MSBuildExtensionsPath)\Microsoft\VisualStudio\v$(VisualStudioVersion)\SSDT\Microsoft.Data.Tools.Schema.SqlTasks.targets" />
+ <Import Condition="'$(NetCoreBuild)' == 'true'" Project="$(NETCoreTargetsPath)\Microsoft.Data.Tools.Schema.SqlTasks.targets"/> 
+ <Import Condition="'$(NetCoreBuild)' != 'true' AND '$(SQLDBExtensionsRefPath)' != ''" Project="$(SQLDBExtensionsRefPath)\Microsoft.Data.Tools.Schema.SqlTasks.targets"/>
+ <Import Condition="'$(NetCoreBuild)' != 'true' AND '$(SQLDBExtensionsRefPath)' == ''" Project="$(MSBuildExtensionsPath)\Microsoft\VisualStudio\v$(VisualStudioVersion)\SSDT\Microsoft.Data.Tools.Schema.SqlTasks.targets"/>

Then you need to add the package references, go to the bottom of the file and before the closing </Project> tag add:

+ <ItemGroup>
+     <PackageReference Condition="'$(NetCoreBuild)' == 'true'" Include="Microsoft.NETFramework.ReferenceAssemblies" Version="1.0.0" PrivateAssets="All"/>
+ </ItemGroup>

Finally you need to add the clean target, which is needed to support dual editing in Visual Studio SQL Server Data Tools (SSDT) and VS Code Database Project Extension (as well as Azure Data Studio):

+ <Target Name="AfterClean">
+     <Delete Files="$(BaseIntermediateOutputPath)\project.assets.json"/>
+ </Target>

For more information on Getting started with the SQL Database Projects extension refer to the Microsoft Learn website.

Setting up GitHub Actions to Build a SQL Database Project

Now that we have our SQL Database Project set up for cross-platform development, let’s move on to setting up the GitHub Actions workflow with an Ubuntu runner. This involves first creating the Actions workflow .yaml file under the folder path .github/workflows/ with a name such as build-sql-project.yaml. We will start with a simple template and add the steps required based on the type of project we chose (SDK-style or Non-SDK-style):

# GitHub Action to build a SQL project
name: Build SQL Project
on:
  push:
    branches:
      - main
  pull_request:
    branches:
      - main
jobs:
  build-sql-project:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4

If you need a refresher on this initial Actions workflow, here’s a breakdown of what each part does:

name: Build SQL Project: This sets the name of the GitHub Action.
on:: This specifies the events that will trigger the GitHub Action. In this case, the action will be triggered on a push or a pull_request to the main branch.
jobs:: This section defines the jobs that the GitHub Action will run. In this case, there’s one job named build-sql-project.
runs-on: ubuntu-latest: This specifies that the job will run on the latest version of the Ubuntu virtual environment provided by GitHub.
steps:: This section defines the steps that the job will execute. In this case, there’s one step.
- uses: actions/checkout@v4: This step uses the checkout action at version 4. The checkout action checks-out your repository under $GITHUB_WORKSPACE, so your workflow can access it. This is typically one of the first steps in most workflows.

SDK-style SQL Project

Start by adding a step to restore the packages referenced by the SQL project, this will restore the required cross-platform SQL Project build tools:

- name: Restore Packages
  run: dotnet restore ./src/EmployeeDbSdk/EmployeeDbSdk.sqlproj

Next we can add the step required to build the SDK-style SQL project:

- name: Build SQL Project
  run: dotnet build --configuration Release ./src/EmployeeDbSdk/EmployeeDbSdk.sqlproj /p:NetCoreBuild=true

Finally we add the step to publish the .dacpac file that can be used in your release pipeline:

- name: Publish SQL Build Artifacts
  uses: actions/upload-artifact@v3
  with:
      name: sql-artifacts
      path: ./src/EmployeeDbSdk/bin/Release

Here’s the complete GitHub Actions workflow to build an SDK-style SQL project:

# GitHub Action to build an SDK-style SQL project
name: Build SDK-style SQL Project
on:
  push:
    branches:
      - main
  pull_request:
    branches:
      - main
jobs:
  build-sql-project:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - name: Restore Packages
        run: dotnet restore ./src/EmployeeDbSdk/EmployeeDbSdk.sqlproj
      - name: Build SQL Project
        run: dotnet build ./src/EmployeeDbSdk/EmployeeDbSdk.sqlproj --configuration Release /p:NetCoreBuild=true
      - name: Publish SQL Build Artifacts
        uses: actions/upload-artifact@v4
        with:
            name: sql-artifacts
            path: ./src/EmployeeDbSdk/bin/Release

SSDT Project (Non-SDK-style)

This method has a script step to bring in the required cross-platform build tools that we don’t get by default, we are effectively just downloading the SDK NuGet package and manually extracting the required tools:

- name: Download SQL Build Tools
  run: |
    # https://www.nuget.org/packages/Microsoft.Build.Sql
    curl -sSL -o /tmp/microsoft.build.sql.zip https://globalcdn.nuget.org/packages/microsoft.build.sql.0.1.15-preview.nupkg
    sudo apt-get install unzip
    mkdir /tmp/microsoft.build.sql
    unzip /tmp/microsoft.build.sql.zip -d /tmp/microsoft.build.sql
    cd /tmp/microsoft.build.sql/tools/netstandard2.1/
    mkdir $GITHUB_WORKSPACE/SqlBuildTools
    cp Microsoft.Data.SqlClient.dll \
        Microsoft.Data.Tools.Schema.Sql.dll \
        Microsoft.Data.Tools.Schema.SqlTasks.targets \
        Microsoft.Data.Tools.Schema.Tasks.Sql.dll \
        Microsoft.Data.Tools.Sql.DesignServices.dll \
        Microsoft.Data.Tools.Utilities.dll \
        Microsoft.SqlServer.Dac.dll \
        Microsoft.SqlServer.Dac.Extensions.dll \
        Microsoft.SqlServer.Server.dll \
        Microsoft.SqlServer.TransactSql.ScriptDom.dll \
        Microsoft.SqlServer.Types.dll \
        System.ComponentModel.Composition.dll \
        System.IO.Packaging.dll \
        $GITHUB_WORKSPACE/SqlBuildTools
    cd $GITHUB_WORKSPACE
    rm -r /tmp/microsoft.build.sql

Next we can add the step required to build the SSDT project, noting we have to pass in the path to the build tools:

- name: Build SQL Project
  run: dotnet build ./src/EmployeeDb/EmployeeDb.sqlproj --configuration Release /p:NetCoreBuild=true /p:NETCoreTargetsPath="$GITHUB_WORKSPACE/SqlBuildTools"