VS 2013 Database Project: Post-Deployment scripts to run based off of build configuration

Question

Is there a method/ mechanism available to run a different set of post-deployment scripts in the SSDT-based database project in Visual Studio 2012/ 2013, based off of the build configuration? I would like to pre-populate my data for testing/ continuous integration by deploying with a "Testing" configuration, but naturally don't want to do this for other environments. I can't seem to find a way to conditionally link a post-deployment script to a build configuration, though, and that seems really strange to me.

Answer 1

You can do this by editing the .sqlproj file used by MSBuild. Then when you build in "Testing" configuration you get different dacpac contents to when building in "Release" mode. For example:

• Create a project and add a PostDeployment script
• Unload the project and edit the .sqlproj file
• Change the ItemGroup containing the PostDeploy script to look as follows:

<ItemGroup Condition=" '$(Configuration)' == 'Release' "> <PostDeploy Include="Debug.PostDeployment1.sql" /> </ItemGroup>

• Build in Debug configuration and unpack the dacpac: no postdeploy.sql file will be included
• Build in Testing configuration and unpack the dacpac: the postdeploy.sql file will be there.

Answer 2

You can use SQLCMD variables as well to do something similar. If the value == "Test", run one set of values. If not, run something else. I blogged about that here:

http://schottsql.blogspot.com/2013/05/trick-to-not-run-prepost-sql-on-publish.html

It's not done on build and the scripts are always included this way, but if that's not an issue it gives you a way to easily write the code to run on various environments easily.