How to pass a variable to sql deploy on devops?

Question

I need that the post deploy script, part of the database project, to set some specific things depending on the environment it will run.

How pass an environment variable that the script can access?

Here what i'm trying to do. the yaml file:

- task: SqlDacpacDeploymentOnMachineGroup@0   
  displayName: Install database
  inputs:
    TaskType: 'dacpac'
    DacpacFile: '**/app-db.dacpac'
    TargetMethod: 'server' 
    ServerName: '(localdb)\MSSQLLocalDB'
    DatabaseName: 'app-dev'
    AuthScheme: 'windowsAuthentication'

The log:

Starting: Install database
==============================================================================
Task         : SQL Server database deploy
Description  : Deploy a SQL Server database using DACPAC or SQL scripts
Version      : 0.3.23
Author       : Microsoft Corporation
Help         : https://learn.microsoft.com/azure/devops/pipelines/tasks/deploy/sql-dacpac-deployment-on-machine-group
==============================================================================
*** Could not deploy package.
Warning SQL72013: The following SqlCmd variables are not defined in the target scripts: env.
Error SQL72014: .Net SqlClient Data Provider: Msg 137, Level 15, State 2, Line 14 Must declare the scalar variable "@env".
Error SQL72045: Script execution error.  The executed script:
IF (@env = 'DEV')
    BEGIN
        ...
    END

score 3 · Accepted Answer · answered Dec 02 '21 at 11:23

Here the complete solution i didn't find anywhere:

Declare a variable for the dacpac. Inside Visual studio, go to the property project page, tab SQLCMD Variables. Add the env variable like this:
In the sql script, write the variable inside a string, so it won't break the build with a SQL syntax error. It is not a SQL variable. The string '$(env)' will be replaced as a token before it runs:
```
if ('$(env)' = 'DEV') ...
```
You can test it still in visual studio by publishing the project.

Finally go the deploy task definition and setup the variable like this:

- task: SqlDacpacDeploymentOnMachineGroup@0   
  displayName: Install database
  inputs:
    TaskType: 'dacpac'
    DacpacFile: '**/app-db.dacpac'
    TargetMethod: 'server' 
    ServerName: '(localdb)\MSSQLLocalDB'
    DatabaseName: 'app-dev'
    AuthScheme: 'windowsAuthentication'
    AdditionalArguments: /v:env=DEV

This answer combined with this https://stackoverflow.com/a/34145220/3298119 led me to a solution. — Baz Guvenkaya, Feb 23 '22 at 06:05

How to pass a variable to sql deploy on devops?

1 Answers1