SqlPackage in development pipelines

SqlPackage is a command-line utility that automates several database development tasks and can be incorporated into CI/CD pipelines.

Virtual environments

Note

Utilizing a standalone installation of SqlPackage for pipeline automation is recommended over using the SqlPackage executables bundled with other applications, including SQL Server Management Studio or Visual Studio. The standalone installation of SqlPackage is updated more frequently and is not tied to the release cadence of other applications.

On Windows, the standalone install of SqlPackage is available on the path C:\Program Files\Microsoft SQL Server\160\DAC\bin (DacFx.msi) or %USERPROFILE%\.dotnet\tools (dotnet tool). On Linux, the standalone install of SqlPackage is available on the path ~/.dotnet/tools (dotnet tool). In both Windows and Linux environments, if you download the self-contained .zip SqlPackage for .NET Core, you can extract the executable to a location of your choosing.

Managed virtual environments

The virtual environments used for GitHub Actions hosted runners and Azure Pipelines VM images are managed in the runner-images GitHub repository. SqlPackage is included in several environments including windows-latest and ubuntu-latest. Updates to the images in runner-images are made within a few weeks of each SqlPackage release.

Self-hosted virtual environments

If you're utilizing SqlPackage in a self-hosted virtual environment such as a self-hosted Azure DevOps agent, it's recommended to update the application regularly to maintain the environment with the latest version.

Tracking deployments

There are a few files related to SqlPackage that can be captured as pipeline artifacts to create pipeline execution reproducibility and improve deployment tracking. The implementation and use cases vary dependent on your specific architecture and automation environment.

• Dacpac file
• Diagnostic file output from any action: Use the /DiagnosticsFile: parameter on any SqlPackage action, see below example
• Output from script action prior to publish action: Use the Script SqlPackage action before invoking a publish action

Other SqlPackage examples

Checking the SqlPackage version

During troubleshooting efforts, it's important to know the SqlPackage version is in use. Capturing this information can be done by adding a step to the pipeline to run SqlPackage with the /version parameter. Examples are given below based on the Microsoft and GitHub managed environments, self-hosted environments may have different installation paths for the working directory.

Azure Pipelines

When the script keyword is used in an Azure Pipeline, a step can be added to an Azure Pipeline that outputs the SqlPackage version number.

- script: SqlPackage /version
  workingDirectory: C:\Program Files\Microsoft SQL Server\160\DAC\bin\
  displayName: 'get sqlpackage version'

GitHub Actions

By using the run keyword in a GitHub Action workflow, a step can be added to a GitHub Action that outputs the SqlPackage version number.

- name: get sqlpackage version
  working-directory: C:\Program Files\Microsoft SQL Server\160\DAC\bin\
  run: ./SqlPackage /version

Obtaining SqlPackage diagnostics in a pipeline agent

Diagnostic information from SqlPackage is available in the command line through the parameter /DiagnosticsFile, which can be used in virtual environments such as Azure Pipelines and GitHub Actions. The diagnostic information is written to a file in the working directory. The file name is dictated by the /DiagnosticsFile parameter.

Azure Pipelines

Adding the /DiagnosticsFile parameter to the "Additional SqlPackage Arguments" field in the Azure Pipeline SqlAzureDacpacDeployment configuration will cause the SqlPackage diagnostic information to be written to the file specified. Following the SqlAzureDacpacDeployment task, the diagnostic file can be made available outside of the virtual environment by publishing a pipeline artifact as seen in the example below.

- task: SqlAzureDacpacDeployment@1
  inputs:
    azureSubscription: '$(azuresubscription)'
    AuthenticationType: 'server'
    ServerName: '$(servername)'
    DatabaseName: '$(databasename)'
    SqlUsername: '$(sqlusername)'
    SqlPassword: '$(sqladminpassword)'
    deployType: 'DacpacTask'
    DeploymentAction: 'Publish'
    DacpacFile: '$(Build.Repository.LocalPath)\$(dacpacname).dacpac'
    AdditionalArguments: '/DiagnosticsFile:$(System.DefaultWorkingDirectory)/output.log'
    IpDetectionMethod: 'AutoDetect'

- task: PublishPipelineArtifact@1
  inputs:
    targetPath: '$(System.DefaultWorkingDirectory)/output.log'
    artifact: 'Diagnostic File'
    publishLocation: 'pipeline'

After the pipeline run, the diagnostic file can be downloaded from the run summary page under "Published Artifacts".

GitHub Actions

Adding the /DiagnosticsFile parameter to the "arguments" field in the GitHub Action sql-action configuration will cause the SqlPackage diagnostic information to be written to the file specified. Following the sql-action task, the diagnostic file can be made available outside of the virtual environment by publishing an artifact as seen in the example below.

- name: Azure SQL Deploy
  uses: Azure/sql-action@v2
  with:
    # The connection string, including authentication information, for the Azure SQL Server database.
    connection-string: ${{ secrets.AZURE_SQL_CONNECTION_STRING }}
    # Path to DACPAC file to deploy
    path: .\DatabaseProjectAdventureWorksLT\bin\Release\DatabaseProjectAdventureWorksLT.dacpac
    action: publish
    # additional SqlPackage arguments
    arguments: /DiagnosticsFile:DatabaseProjectAdventureWorksLT/DiagnosticLog.log

- uses: actions/upload-artifact@v2
  with:
    name: 'DiagnosticLog.txt'
    path: 'DatabaseProjectAdventureWorksLT/DiagnosticLog.log'

Update SqlPackage on the pipeline agent

In some scenarios, the current version of SqlPackage installed in the pipeline environment may be insufficient. If the environment can't be directly modified, an additional step can be used to install a newer version of SqlPackage during the pipeline run. It's important to run the install step before running any DacPac or BacPac operations in the pipeline. This task can be combined with a step to check the version to ensure that the upgrade completed as expected.

Azure Pipelines

When the PowerShell task is used in an Azure Pipeline, a step can be added to an Azure Pipeline that downloads the desired MSI and installs it silently.

- task: PowerShell@2
  displayName: 'upgrade sqlpackage'
  inputs:
    targetType: 'inline'
    script: |
      # use evergreen or specific dacfx msi link below
      wget -O DacFramework.msi "https://aka.ms/dacfx-msi"
      msiexec.exe /i "DacFramework.msi" /qn

Next steps

• Learn more about SqlPackage