Search code examples
sql-serversql-server-data-toolssqlcmddatabase-projectdacpac

Access Build variables inside Pre and Post deployment scripts SSDT

I have just set up an SSDT project which I want to use to create local databases on the SQL server hosted locally on my machine.

I want to add some pre- and post- deployment SQL scripts for initialization and cleanups.

Since, the server and the database name can change, I have defined two build variables using the project properties each for the target server and target database.

However, I can't seem to access them inside the post deployment scripts.

The syntax below won't build the project - 

use [$(TargetDatabaseName)]

This builds, but then fails while publishing - 

use ['$(TargetDatabaseName)']

and the error says the ''myTargetDB'' doesn't exist (myTargetDB was passed as a value at the time of publishing)

This might be a trivial thing but I am just not able to get around it. I am on SQL server 2016 if that matters.

Solution

  • Make sure that you put both scripts in SQLCMD mode. See the image below surrounding with red.

    enter image description here

    Once your target variable is defined, see surrounding with blue in the image above, it can be safely used in the PostDeployment script, see the image below surrounding with blue. enter image description here

    If you have any questions, feel free to contact me.