SQL DACPAC passing in sqlcmd variables

[nichi06261 (Nichi0626)]

I am using the SQL - DEPLOY DACPAC template and the sqlcmd variables are not getting passed in.

Do I put them in the “Additional deployment contributor arguments” with the syntax /v:variable=myvariable ?

[Kenneth_Bates]

Hi Nichi,

Thanks for getting in touch! From my understanding of this step, you should be able to add in these variables simply by adding some variables into your project using the format SqlCmdVariable.<VariableName>. The script then finds all variables in your project with the SqlCmdVariable. prefix and adds them in. I’ve pasted the section doing this below.

# Get all SqlCmdVariables
        $sqlCmdVariables = $OctopusParameters.Keys -imatch "SqlCmdVariable.*"
        
        # Check to see if something is there
        if ($null -ne $sqlCmdVariables)
        {
        	Write-Output "Adding SqlCmdVariables ..."
            
            # Check to see if the deploy options property is null
            if ($null -eq $dacProfile.DeployOptions)
            {
            	# Create new Microsoft.SqlServer.Dac.DacDeployOptions object
                $dacProfile.DeployOptions = New-Object Microsoft.SqlServer.Dac.DacDeployOptions
            }
            
            # Loop through the variable collection
            foreach ($sqlCmdVariable in $sqlCmdVariables)
            {
            	# Add variable to the deploy options
                $sqlCmdVariableKey = $sqlCmdVariable.Substring(($sqlCmdVariable.ToLower().IndexOf("sqlcmdvariable.") + "sqlcmdvariable.".Length))
                
                Write-Output "Adding variable: $sqlCmdVariableKey with value: $($OctopusParameters[$sqlCmdVariable])"
                
                $dacProfile.DeployOptions.SqlCommandVariableValues.Add($sqlCmdVariableKey, $OctopusParameters[$sqlCmdVariable])
            }
        }

I hope this helps! Let me know how you go or if you have any further questions or concerns moving forward.

Best regards,

Kenny