When you use SSDT to deploy database changes you can include a pre/post deploy script which is run after the dacpac and database has been deployed. The process looks like:

• 1. sqlpackage.exe compares dacpac to database
• 2. sqlpackage.exe generates a deploy script
• 3. sqlpackage.exe runs pre-deployment script
• 4. sqlpackage.exe runs deploy script (generated in step 2)
• 5. sqlpackage.exe runs post-deploy script

This happens whether you get sqlpackage.exe to do the deployment or whether you generate a script for later deployment.

If you generate a script for later deployment what you end up with is one long script with the pre-deploy script, the deploy script and the post-deploy script all in one long script.

There are times when you want the pre-deploy script to run BEFORE the compare happens. For SSDT to allow a pre-compare script, I believe it would cause quite a bit of confusion as the script would need to be run before things like getting a change report or generating a script for later deployment but what if the pre-compare script contains something that shouldn’t be run until the deployment time? Best case secenario is confusion, worst case is deploying something at the wrong time, forget about cases where deploy scripts are generated on a mirrored copy of production!

Instead what we should do is build our own pre/post sqlpackage.exe scripts so the CI process would be something like:

• 0. CI build runs pre-sqlpackage.exe script
• 1. sqlpackage.exe compares dacpac to database
• 2. sqlpackage.exe generates a deploy script
• 3. sqlpackage.exe runs pre-deployment script
• 4. sqlpackage.exe runs deploy script (generated in step 2)
• 5. sqlpackage.exe runs post-deploy script
• 6. CI build runs post-sqlpackage.exe script

Doing it this way we know what and when each script will be run and in which cases it is fine and where it should definetley not happen.

To fit this into SSDT we can keep the script in the project and set the “Build Action” to none and the “Copy to Output Directory” to “Copy Always” or “Copy if Newer”, this will mean that the script is not in the dacpac but it is in the output directory so you can move the file(s) with the dacpac and run using whatever you run sqlpackage.exe with.

I did play around with getting the script into the dacpac it is hard to get it into the dacpac and hard to get it out using the built in tools, you could use the .net packaging api https://msdn.microsoft.com/en-us/library/system.io.packaging(v=vs.110).aspx but I do not know if sqlpackage gets upset at extra files in the dacpac.

Comments:

Anonymous

December 3, 2015 - 16:38

Great article and exactly

Great article and exactly what I am looking to do for a change tracking script. Could you please expand on how you get this script to execute before the compare happens?

Ed Elliott

December 4, 2015 - 12:18

sure, I will post back here

sure, I will post back here when I do :)