Azure DevOps SQL Deployment: How to Substitute SQLCMD Variables with Pipeline Variables

Data and Databases
1

I am working on a SQL Server project in Visual Studio that contains SQLCMD variables. When attempting to deploy it as a DACPAC, I encounter an error message. The pipeline defines a variable named ‘MemOptimizedPath’, and I am using the task SqlDacpacDeploymentOnMachineGroup@0 for the deployment process. It’s worth noting that the DACPAC builds correctly during the build task. How can I substitute the SQLCMD variables with the pipeline variables during the deployment in the pipeline?

jobs:
  - job: DeployDatabase
    displayName: 'Deploy Red and Yellow Databases'
    pool: '<pool>'

    variables:
      MemOptimizedPath: 'H:\SQLData\TestDatabaseMemoryOptimized'

    steps:
    - task: DownloadBuildArtifacts@1
      inputs:
        buildType: current
        downloadType: single
        artifactName: 'drop'
        downloadPath: '$(Build.ArtifactStagingDirectory)'

    - task: SqlDacpacDeploymentOnMachineGroup@0
      displayName: 'Deploy Database'
      inputs:
        TaskType: dacpac
        DacpacFile: '**/*.dacpac'
        TargetMethod: connectionString
        ConnectionString: '${{ variables.yellowConnectionString }}'
2

Hey, Zoe! Have you tried using the SqlCmdVariables input directly in your pipeline? I’ve read a bit about it, and it seems you can map pipeline variables like ${MemOptimizedPath} to SQLCMD variables this way. Would that align with what you’re trying to accomplish? Curious to see if it works!

3

It seems like you need to make use of the additional parameters available in the SqlDacpacDeploymentOnMachineGroup@0 task. You should be able to specify your SQLCMD variables in the SqlCmdVariables input parameter. This can be done by setting it like this:

SqlCmdVariables: 'MemOptimizedPath=$(MemOptimizedPath)'

This will substitute your pipeline variable into the SQLCMD variable during deployment. Make sure that your pipeline variable is defined at the correct scope so it is accessible during the deployment job.

4

Hey Zoe! Another thing you might want to check is if the variable syntax being used fits your YAML pipeline format precisely. If there’s a small typo or misconfiguration, sometimes Azure DevOps acts up. Also, ensure there’s no case sensitivity issue in variable names. These can be easy to miss!