Access Build variables inside Pre and Post deployment scripts SSDT

Question

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.

Answer 1

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

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.

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

Answer 2

There is a predefined variable, $(DatabaseName), for the name of the target database. You don't need to create your own; and even if you do, you would need to set the same value to both of them.

Not sure about the target server. In most cases, SQL scripts are generated with the assumption that connection to the correct server is already established. Sure, you can change the current server using something like :connect $(TargetServerName), but I think it will only lead to confusion (and I'm not sure it will work, actually).

The only exception I can think of is that you can't use SQLCMD variables to parameterise the logical/physical names of the database files - these have to be hardcoded.

All other variables, if declared in the project properties, should be accessible everywhere. Below is a fragment of a post deploy from one of my projects:

use [master];
go

print 'Switching database ownership to sa...';
GO
alter authorization on database::[$(DatabaseName)] to [sa];
go


use [$(DatabaseName)];
go

print 'Creating database master key...';
go

-- Create database master key
create master key encryption by password = '$(DMK_Key)';
go

print 'Running database setup...';
go
exec dbo.init_database;
go

---

It is possible, however, that you are trying to reference another database, located on a different server. If that's the case, you need to follow a different approach, namely: built a project for that remote database and add its DACPAC to the list of project references, using the Add database reference... menu. There, you will be able to specify variables for both the (linked) server and the database name.