Pass variables to the SqlAzureDacpacDeployment@1 task of a SQL Server instance while running an Azure Pipeline

Question

I want to pass variables to the SqlAzureDacpacDeployment@1 (or DotNetCoreCLI@2) task of an SQL Server instance while running an Azure Pipeline.

This variable is going to be the env (dev, acceptance, prod) for which the pipeline is running.

I have a pipeline which looks like this (not identical):

variables:
  ARMSvcCnnName: YourAzureResourceManagerServiceConnectionName
  AzureSQLServerName: YourAzureSQLSeverName
  AzureSQLDBName: YourAzureSQLDBName
  buildPlatform: 'Any CPU'
  buildConfiguration: 'Release'

pool:
  vmImage: 'windows-latest'

- task: DotNetCoreCLI@2
            displayName: 'Build Project: *.sqlproj'
            inputs:
              command: 'build'
              projects: '**\*.sqlproj'

- task: SqlAzureDacpacDeployment@1
  displayName: 'Deploy Azure SQL DB'
  inputs:
    azureSubscription: '$(ARMSvcCnnName)'
    AuthenticationType: 'servicePrincipal'
    ServerName: '$(AzureSQLServerName).database.windows.net'
    DatabaseName: '$(AzureSQLDBName)'
    deployType: 'DacpacTask'
    DeploymentAction: 'Publish'
    DacpacFile: '**\*.dacpac'
    AdditionalArguments: '/v:ETLUserPassword="$(sql-password)" /v:AppUserPassword="$(sql-password)"'
    IpDetectionMethod: 'AutoDetect'
    DeleteFirewallRule: false

I was wondering if I could dynamically pass the env variable, which comes from the CI/CD interface to each of this task in order to use it in a T-SQL query like this:

IF @myVariable = 'dev'
BEGIN
    PRINT 'DEV';
END
ELSE if @myVariable = 'acc'
BEGIN
    -- Other commands here
    PRINT 'ACC';
END
ELSE 
BEGIN
    -- Other commands here
    PRINT 'PROD';
END

I was thinking of a sqlcmd together with PublishProfile but I miss a proper tutorial

Mb:

SqlAdditionalArguments: # string. Optional. Use when TaskNameSelector = SqlTask. Additional Invoke-Sqlcmd Arguments.

InlineAdditionalArguments: # string. Optional. Use when TaskNameSelector = InlineSqlTask. Additional Invoke-Sqlcmd Arguments.

https://learn.microsoft.com/en-us/azure/devops/pipelines/tasks/reference/sql-azure-dacpac-deployment-v1?view=azure-pipelines

Answer 1

In your SqlAzureDacpacDeployment@1 task, you are publish dacpac file to azure sql, it invokes SqlPackage.exe for the deployment, it doesn't support to pass custom variable to sql script.

To run the SQL Script file against the Azure SQL Database, you should choose SqlTask for the deployType, it uses Invoke-Sqlcmd command, you can specify the sql script, and pass custom variables in SqlAdditionalArguments. Task as below:

- task: SqlAzureDacpacDeployment@1
  inputs:
    azureSubscription: 'ARMConn-NonProd'
    AuthenticationType: 'server'
    ServerName: 'testsqlserver1.database.windows.net'
    DatabaseName: 'testdb1'
    SqlUsername: 'test'
    SqlPassword: '$(sqlpwd)'
    deployType: 'SqlTask'
    SqlFile: '**\test.sql'
    SqlAdditionalArguments: '-Variable "myVariable=$(env)"'
    IpDetectionMethod: 'AutoDetect'

To output the variable in pipeline console for checking, changed the sql script content as below:

DECLARE @OutputMessage VARCHAR(50)

IF '$(myVariable)' = 'dev'
BEGIN
    SET @OutputMessage = 'DEV'
END
ELSE IF '$(myVariable)' = 'acc'
BEGIN
    SET @OutputMessage = 'ACC'
END
ELSE 
BEGIN
    SET @OutputMessage = 'PROD'
END

SELECT @OutputMessage AS 'Environment'

The value is output in pipeline:

You can find more details in the task guide.