SQL Post Deployment SQL CMD Variables and YAML Task

I am deploying a SQL Server Database to Azure and part of that deployment is to load static data into some tables as a Post Deployment Task. My YAML is set up so we can deploy at "client by client basis" manually executing the Pipeline. Client Post deployment data is stored in a client folder with the SQL Project in SQL files.

My Yaml Task:

  - task: SqlAzureDacpacDeployment@1
    inputs:
      azureSubscription: ${{ parameters.azureSubscription }}
      serverName: ${{ parameters.serverName }}
      databaseName: ${{ parameters.databaseName }}
      sqlUsername: ${{ parameters.sqlUsername }}  
      sqlPassword: 'pwd'
      deployType: 'DacpacTask'
      DacpacFile: ${{ parameters.dacpacFile }}
      DeploymentAction: ${{ parameters.DeploymentAction }}
      AdditionalArguments: /v:RunPostDeployment=${{ parameters.RunPostDeployment }} /v:tenant=${{ parameters.tenant }}
    displayName: Deploy SQL DacPac

My two SQL CMD variables are RunPostDeployment and defaulted to true and tenant defaulted to CompanyA. CompanyA being a folder with SQL script data.

My Post Deployment Controller SQL File:

IF '$(RunPostDeployment)' = 'True'
BEGIN
    -- Set Vars
    :r .\variables.sql
    
    -- Tenant and Stages
    :r ..\config_data\$(tenant)\tenant_and_stage.sql
    :r .\tenant.sql
    :r .\stage.sql

    -- Pipeline and Parameters
    :r ..\config_data\$(tenant)\pipeline_and_parameters.sql
    :r .\pipeline.sql
    :r .\pipeline_parameter.sql

    -- Column Mappings
    :r ..\config_data\$(tenant)\column_mappings.sql
    :r .\column_mapping.sql
END

Running my YAML Pipeline and selecting CompanyA, everything works like a charm. But selecting CompanyB, no data is loaded. If I set tenant default to CompanyB, then I encounter the reverse. It's almost appears like the YAML task where tenant is set, isn't working. I've checked the usual - spelling, case etc etc and all is fine.

Further testing suggests that the values being passed through on the YAML task are NOT being passed. Removing defaults and supplying false for RunPostDeployment yields the opposite result!

Some other folk may need this answer, but the solution was in the VSBuild@1 YAML Task, not the deployment!

In the controller file above, I am using $(tenant) to dynamically pick the client folder and Json data embedded a SQL Variable via late binding e.g. :r ..\config-data\$(tenant)\pipelines-and-parameters.sql. ie. rather than having a controller file that grows with every new client, late binding deals solves that.

As the SQL Cmd variable (tenant) had a default, it wasn't picking up the folder with the files (SQL Variable with Json) I wanted and always used the default Client. When it got to the dacpac deploy, I could supply any SQL Cmd value, but it was irrelevant as the dacpac had already been created with its SQL Post Deployment SQL File!

Two elements were adjusted. Firstly in the sqlproj file:

  <ItemGroup>
    <SqlCmdVariable Include="RunPostDeployment">
      <Value>$(RunPostDeployment)</Value>
      <DefaultValue>False</DefaultValue>
    </SqlCmdVariable>
    <SqlCmdVariable Include="tenant">
      <Value>$(tenant)</Value>
      <DefaultValue>default</DefaultValue>
    </SqlCmdVariable>
  </ItemGroup>

Value matches the variable name. Rather than some random value that VS Code sets e.g. sql_cmd_6!

Secondly in VSBuild@1, I supply the values I want to set as args, so when the dacpac is compiled, it takes the correct client folder (Json SQL Variable):

- task: VSBuild@1
  inputs:
    solution: $(sqlProjectFile)
    msbuildArgs: '/p:Configuration=$(buildConfiguration) /p:SqlCmdVariables=RunPostDeployment=${{parameters.RunPostDeployment}};tenant=${{parameters.tenant}}'
    platform: 'Any CPU'
    configuration: $(buildConfiguration)