Anyway, the reason for the post is that I have just learned something rather interesting about how SSDT, VS, MSBuild and SqlPackage.exe co-operate to get scripts added to the beginning and end of a database change script.
I have just started using tSQLt to write database unit tests after years of using the SQL Server Unit Test in the Visual Studio Test Project and my plan is to integrate the two different frameworks so that I can benefit from the fakes and assertion utilities in tSQLt but still have all my tests visible in the Visual Studio Test Explorer. I needed to have tSQLt deployed as part of the database project to make this happen and I wanted it to be extremely easy to upgrade tSQLt when a new version is released.
Add the tSQLt.class.sql downloaded from tSQLt.org as a Post-Deployment script and have the project decide whether to include it based on a project variable. Sounds simple but there is a catch - you can't simple add some conditional logic to the Post-Deployment script like this:
IF ('$(UseTestFramework)' = 'true')
It would be nice if you could but by adding a TSQL statement to the script SSDT treats the tSQLt.class.sql as embedded TSQL and throws an exception because the file is crammed with GO statements. So you may try this:
In this case the sqlcmd variable value can be set differently in each environment publish settings file and for the environments where it is not needed an empty substitute file can be used. The problem is that SqlPackage.exe uses the DEFAULT value of the sqlcmd variable to evaluate the expression, not the value set in a publish settings file; so you end up with the the same result whatever you do.
It is similar but with a twist: you need to set the value of a sqlcmd variable in the database project file using an MSBUILD variable that can be determined at build time. The legendary Gert describes the solution here: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/745a2189-62db-4739-8437-8aac16affa05/conditional-post-deployment-for-unit-test?forum=ssdt
So, the steps to use conditional logic to determine if a particular script is included are quite simple:
- Add a database project variable named Configuration
- Unload the project
- Set the value of the variable to $(Configuration) - that's the MSBuild variable
- Reload the project
- Add a Debug.sql and a Release.sql file as post-deployment scripts
- Use the $(Configuration) sqlcmd variable in the post-deployment script to include the correct file based on the configuration of the build
The downside is that your optional execution paths are determined by the number of configuration types you have, rather than by the content of your publish settings file...but it is better than nothing!