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.

Iridescent

Answer 1

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.

Cyclopropane

Hmm... how do you set the SQLCMD value to be the build configuration? I tried to use $(Configuration) but it just parsed that value and stored "Debug". – Iridescent

We create Publish Profiles to do all of that. We then build using that publish profile. I know there's an option to pass SQLCMD values, but not sure what it is off the top of my head. We more often build first, then publish w/ SQLPackage to set values. – Cyclopropane

Answer 2

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.

We