It isn't a Pre compare script
When you use SSDT to deploy your dacpac, a pre deployment script lets you run some T-SQL scripts before the script which brings your database up to date with your dacpac.
The important thing to note is that the process is:
- sqlpackage compares the dacpac to the database
- sqlpackage generates a list of changes to make
- sqlpackage generates the T-SQL statements to make those changes
- sql package either creates a script for later execution or deploys the changes
The script that is created *or* is executed contains, 0 or 1 pre deployment scripts, the statements to generate the required changes and then 0 or 1 post deployment scripts.
The pre deployment script is not actually executed before the compare, that would be a pre compare script and if you were only generating a script which can be manually reviewed and deployed later, would you want the pre compare script to be executed? I would say no.
If the script is not run before a compare when generating a script, should it be run before a compare which will then be automatically deployed? I would say probably yes but that would be confusing and I am sure it would cause lots of problems for people.
I need to run a script before the compare
Ok well there are two general approaches, the first is to have a script you run before you call sqlpackage.exe. This is the simplest and if you are sure your script is fine to run then do it.
The second is to stage changes in releases and make the changes you would in the pre deploy script as part of a normal deployment and then in a future deployment make the second set of changes, this doesn't work in all scenarios but can help if you are disciplined about making sure that the second part actually happens.
Overall when using sqlpackage there is a lot of flexibility but it isn't going to do everything for you, use good testing processes to catch when things go wrong and make sure you understand the tooling to work out what does and doesn't work for you.
