CODEWITHSUNDEEP
sql-serverazure-devopsazure-pipelines
Andy S
Andy S

Reputation: 29

Azure Devops SQL Deployment Replace SQLCMD Variables with Pipeline variables

I have SQL Server project on my visual studio which has SQLCMD variables. When I try to deploy this as dacpac I get the following error.

enter image description here

The variable 'MemOptimizedPath' is defined on my pipeline. I am using the SqlDacpacDeploymentOnMachineGroup@0 task to deploy the dacpac. NB: On the build task the dacpac is created successfully.

The question is how do I replace the SQLCMD variables with pipeline variables on 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 }}'

Upvotes: 0

Views: 40

Answers (1)

bryanbcook
bryanbcook

Reputation: 17953

For the dacpac to pick up the variables, you need to specify the variables on the command-line using a syntax /v:<name>=<value>. The SqlDacpacDeploymentOnMachineGroup@0 supports this through the AdditionalArguments argument.

Here's how I've programmatically set my variables:

variables:
  SQLCMDARG_MemOptimizedPath: 'H:\SQLData\TestDatabaseMemoryOptimized'

steps:
- pwsh: |
    $additionalArgs = "";

    # Grab all variables that start with SQLCMDARG_
    Get-ChildItem -Path Env:SQLCMDARG_* | ForEach-Object {
      $name = $_.name.Replace("SQLCMDARG_", "") # trim off prefix
      $value = $_.value

      # append argument to temporary value
      $additionalArgs += " /v:$name=$value"
    }
 
    # write args to a variable
    Write-Host "##vso[task.setvariable variable=SQLCMDARGS]$additionalArgs"
  displayName: Dynamically build SQLCMD args

- task: SqlDacpacDeploymentOnMachineGroup@0
  displayName: 'Deploy Database'
  inputs:
    TaskType: dacpac
    DacpacFile: '**/*.dacpac'
    TargetMethod: connectionString
    ConnectionString: '${{ variables.yellowConnectionString }}'
    AdditionalArguments: $(SQLCMDARGS) # include the args on the command-line

Upvotes: 0

Related Questions