How do you reference defined variables in a SQL Server Database Project?

Question

I've read many questions on this such as:

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/da4bdb11-fe42-49db-bb8d-288dd1bb72a2/sqlcmd-vars-in-create-table-script?forum=ssdt

and

How to run different pre and post SSDT pubish scripts depending on the deploy profile

What I'm trying to achieve is a way of defining a set of scripts based on the environment being deployed to. The idea is that the environment is passed in as a SQLCMD variable as part of the azure-devops pipeline into a variable called $(ServerName), which I've setup in the sql server database project under properties with a default of 'DEV'.

This is then used in the post deployment script like this:

:r .\PostDeploymentScripts\$(ServerName)\index.sql

This should therefore pick up the correct index.sql file based on the $(ServerName) variable. When testing this by publishing and entering 'QA' for the $(ServerName) variable and generating the script it was still displaying the 'DEV' scripts. However, the top of the script showed the variable had been set correctly:

How do I get the post deployment script to reference the $(ServerName) variable correctly so I can dynamically set the correct reference path?

Answer 1

Contrary to this nice post: https://stackoverflow.com/a/54482350/11035005 , it appears that the :r directive is evaluated at compile time and inserted into the DACPAC before the xml profiles are even evaluated so this is not possible as explained.

The values used are the defaults or locals from the build config and can only be controlled from there.