Devops deployment passing arguments\parameters to a SQL Script

Question

I am trying to run a script that creates Environment variables for an SSIS package.
Depending on the destination i.e. SIT, UAT or PRD I want my script to use different variable values depending on the destination. In my script I have a variable @DepoyServer and I want this to be populated as a parameter or argument from Devops when using the Execute SQL Script Task.
My code then looks at what this is set to and sets others common variables for each environment e.g. A database connection string variable will be set to that of the environment. Example script code would be: -

-- DECLARE @DeployServer varchar(100)
Declare @DBConnectionString varchar(500)
IF @DeployServer = 'UAT'
   SET @DBConnectionString = 'ConnectionStringForUAT'

IF @DeployServer = 'PRD'
   SET @DBConnectionString = 'ConnectionStringForPRD'

/*
Code to create environment varaiables and populate the variable with @DBConnectionString
*/

The SQL Script file path is set using: - $(System.DefaultWorkingDirectory)/path/SQLScript.sql

There is a field for Arguments. I have oogled it to death but ll I'm getting are DACPAC examples.

Answer 1

You define the sql script with sqlcmd variable for deploy server.

:SetVar DeployServer UAT

Declare @DBConnectionString varchar(500)
IF $(DeployServer) = 'UAT'
   SET @DBConnectionString = 'ConnectionStringForUAT'

IF $(DeployServer) = 'PRD'
   SET @DBConnectionString = 'ConnectionStringForPRD'

Now, call this script in the sqlcmd tool with right value for the DeplyServer

sqlcmd -v DeployServer ="UAT" -i $(System.DefaultWorkingDirectory)/path/SQLScript.sql