SSDT - to script or not to script!

I have been using SSDT for years, through its various incarnations, and I am a huge fan. I can say I have fond memories of Gert the Data Dude posting his way to blogger awesomeness and me being extremely grateful that he did. Gert has moved on to other parts of the Microsoft universe but the product has survived and seems to be a fully-fledged senior citizen in the Visual Studio landscape. Worryingly, Visual Studio has also started to entertain a new suitor, Red-Gate, and their devops offering is quite different from the model-based SSDT project...we shall see what happens there.

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.

The Requirement:
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.

The Plan:
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')
:r .\tSQLt.class.sql

The Problem:
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:

:r .\$(TestFrameworkFileName).sql

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.

The Solution:
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:

1. Add a database project variable named Configuration
2. Unload the project
3. Set the value of the variable to $(Configuration) - that's the MSBuild variable
4. Reload the project
5. Add a Debug.sql and a Release.sql file as post-deployment scripts
6. 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!