Skip to content

Create a dacpac for an Azure Synapse Analytics dedicated SQL Pool using GitHub Actions

Reading Time: 6 minutes

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

By the end of this post, you will know how to create a dacpac for a dedicated SQL Pool within GitHub Actions for your CI/CD deployments.

Creating database project for Azure Synapse dedicated SQL Pool

In a previous post I covered how you can create a dacpac for an Azure Synapse Analytics dedicated SQL Pool using Azure Data Studio. After somebody asked a question about creating a dacpac on Twitter.

You can refer back to that post to find out more about creating a database project for a dedicated SQL Pool in Azure Data Studio.

I then did a follow up in another post about how to create a dacpac for an Azure Synapse Analytics dedicated SQL Pool using Azure DevOps. With this in mind, I thought it was only fair that I showed how to do the same thing using GitHub Actions.

In this post I will use the same database project in Azure Data Studio. Picking up where I left off in the previous post.

Synchronize to GitHub instead to create dacpac using GitHub Actions

I had to do a few steps in order synchronize the existing Database Project in Azure Data Studio with GitHub. Because it was currently set to sync with Azure DevOps. First of all, I had to create new repository in GitHub.

New repository in GitHub

Once I had done that, I went a back to the Database Project I had created in the previous post in Azure Data Studio. I went back into Source Control and removed the remote I had set up previously.

Remote options in Source Control

However, this time around I got the URL from GitHub instead of Azure DevOps.

URL from GitHub

I called the remote origin again, just for reasons. Once I had done that, I selected the same sync option that I selected in the previous post. Afterwards, I refreshed my GitHub repository to check the sync had worked.

Database Project in GitHub

Create dacpac for Azure Synapse dedicated SQL Pool using GitHub Actions

Now for the topic at hand. To create a GitHub Action, you need to create a file in the ‘.github/workflows’ subfolder in your repository.

To demonstrate how to create one quickly I went to the ‘Actions’ section as shown below. From there I created a simple workflow by clicking the highlighted button.

Create simple workflow

If you are following along you will notice that a new file has been created.

You can rename this file at the top and then select the Start commit button if you want to see the default template in action. However, I decided to make changes at this point.

So, I changed the build job to the below. Note the similarities between the logic used here and the logic I used in the post on how to do this using Azure DevOps.

  BuildDacpac:
    # The type of runner that the job will run on
    runs-on: windows-latest

    # Steps represent a sequence of tasks that will be executed as part of the job
    steps:
      # Checks-out your repository under $GITHUB_WORKSPACE, so your job can access it
      - uses: actions/checkout@v2
      
      # Find msbuild
      - name: Add msbuild to PATH
        uses: microsoft/setup-msbuild@v1.0.2
        
      # create dacpac using MSBUILD
      - name: Build dacpac for Database Project
        run: |
          msbuild.exe 'Create dacpac from ADS test.sqlproj' /p:Configuration=Release
                  
      # Publish SQLPool artifact containing the contents of the Build results folder
      - uses: actions/upload-artifact@v2
        with:
          name: SQLPool
          path: ${{ github.workspace }}/bin/Release/  

In this example I used a GitHub-hosted runner that runs the latest version of Windows Server. However, I prefer to use self-hosted runners.

In addition, I do have to thank Greg Low for suggesting I look for setup-msbuild. It basically finds the latest version of MSBuild for your agent to use.

Once I had made the above changes, I made an initial commit of the new workflow.

Committing the YAML to create a dacpac for an Azure Synapse Analytics dedicated SQL Pool using GitHub Actions
Initial commit

When I went back to the Actions section, I saw that the workflow had completed.

Creating a dacpac for an Azure Synapse Analytics dedicated SQL Pool using GitHub Actions
All workflows

To view further details, I clicked on the workflow name highlighted above.

Summary of workflow to create a dacpac for an Azure Synapse Analytics dedicated SQL Pool using GitHub Actions
Workflow summary

One key point here is that the experience in viewing an artifact here is different than in Azure DevOps. Because when you click on an artifact here it downloads it. I admit I do prefer the ability to view the contents of an artifact directly in Azure DevOps. At the end of the day, it’s all down to personal choice.

Summary of workflow to create a dacpac for an Azure Synapse Analytics dedicated SQL Pool using GitHub Actions
Download artifact

Deploying the dacpac using GitHub Actions

After testing that the dacpac was created OK I went to deploy it to an existing SQL Pool.

Note: If following along here make sure you have your Azure Synapse Analytics dedicated SQL Pool created first.

Before adding the YAML for the deployment job I first created a secret in GitHub to securely store my connection string.

Adding a secret in GitHub
Adding a secret in GitHub

After adding the secret, I then added the below YAML to the end of the file. Note that it uses the same ‘Azure SQL Deploy’ action that I used in my post about deploying to Azure SQL Database using GitHub Actions.

  DeployDacpac:
    # Set the dependency for the BuildDacpac job
    needs: BuildDacpac
    # The type of runner that the job will run on
    runs-on: windows-latest
    
    # Steps represent a sequence of tasks that will be executed as part of the job
    steps:
    
      - name: Download SQLPool artifact
        # Downloads Data artifact to dacpac folder
        uses: actions/download-artifact@v2.0.9
        with:
          name: SQLPool
          
      # Deploys SQLPool dacpac
      - name: Deploy SQLPool Dacpac
        uses: Azure/sql-action@v1
        with:
          # Name of the SQL Server name
          server-name: '{YOUR SYNAPSE DEDICATED SQL ENDPOINT}'
          # The connection string, including authentication information, for the SQL Server database.
          connection-string: '${{ secrets.AZURESQLPOOL_CONNECTION_STRING }}'
          # Path to DACPAC file. *.dacpac or a folder to deploy
          dacpac-package: 'Create dacpac from ADS test.dacpac'
          # In case DACPAC option is selected, additional SqlPackage.exe arguments that will be applied. When SQL query option is selected, additional sqlcmd.exe arguments will be applied.
          arguments: '/p:IncludeCompositeObjects=true' 

When you are adding new actions it’s worth checking that you are using the latest version. For example, the ‘Download SQLPool artifact’ action above. Which you can find in the Marketplace if you search for ‘Download a Build Artifact’.

Download a Build Artifact Action
Download a Build Artifact Action

You will also notice in the above code sample that I specify {YOUR SYNAPSE DEDICATED SQL ENDPOINT}. Just like I did in my previous post about doing this in Azure DevOps.

To clarify, this is the dedicated SQL endpoint that you can find in your Synapse workspace overview page. I showed the overview page before in a post where I did a five-minute crash course about Synapse Studio.

Note that I have used the full name of the dacpac here for certainty. However, as you can see in the comments you can also use other means.

Completed dacpac workflow in GitHub Actions

Once I committed my changes the workflow ran again and completed as below. So, I can update the database project in Azure Data Studio and have the updates deployed to my dedicated SQL Pool using GitHub Actions.

Create a dacpac for an Azure Synapse Analytics dedicated SQL Pool using GitHub Actions
Completed workflow

Of course, in real life your workflow can be more complicated than this. I recommend experimenting yourself.

Last Saturday myself and Sander Stad presented ‘GitHub Actions Duet – LA Edition’ at SQL Saturday LA.

I have added a link for it below in case anybody wants to watch a recent GitHub video that’s related to this post. For those who have seen a version of this session before I can assure you this one will be different.

Final word about creating a dacpac for Azure Synapse dedicated SQL Pool using GitHub Actions

I hope this post about how you can create a dacpac for an Azure Synapse Analytics dedicated SQL Pool using GitHub Actions proves useful.

Of course, if you have any comments or queries about this post feel free to reach out to me.

Published inGitHub Actions

2 Comments

  1. […] We were asked if we thought GitHub and Azure DevOps were eventually going to be merged into one product. You can hear our reply to this question in the video from SQL Saturday LA. I shared a link for that video in my last post about using GitHub Actions. […]

  2. […] In this example I use a requirements-based test suite with Azure Synapse Analytics. For this example, I am going to use the GitHub repository I created in another post. Which was about creating a dacpac for an Azure Synapse Analytics dedicated SQL Pool using GitHub Actions. […]

Leave a Reply

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