How can I conditionally include large scripts in my ssdt post deployment script?

Question

In our SSDT project we have a script that is huge and contains a lot of INSERT statements for importing data from an old system. Using sqlcmd variables, I'd like to be able to conditionally include the file into the post deployment script.

We're currently using the :r syntax which includes the script inline:

IF '$(ImportData)' = 'true'
BEGIN
  :r .\Import\OldSystem.sql
END

This is a problem because the script is being included inline regardless of whether $(ImportData) is true or false and the file is so big that it's slowing the build down by about 15 minutes.

Is there another way to conditionally include this script file so it doesn't slow down the build?

Answer 1

Rather than muddy up my prior answer with another. There is a special case with a VERY simple option.

Create separate SQLCMD input files for each execution possibility. The key here is to name the execution input files using the value of your control variable.

So, for example, your publish script defines variable 'Config' which may have one of these values: 'Dev','QA', or 'Prod'.

Create 3 post deployment scripts named 'DevPostDeploy.sql', 'QAPostDeploy.sql' and 'ProdPostDeploy.sql'.

Code your actual post deploy file like this:

:r ."\"$(Config)PostDeploy.sql

This is very much like the build event mechanism where you overwrite scripts with appropriate ones except you don't need a build event. But you are dependent upon naming your scripts very specifically.

Answer 2

The scripts referenced using :r are always included. You have a couple of options but I would first verify that if you take the script out it improves the performance to where you want it to get to.

The simplest approach is to just keep it outside of the whole build process and change your deploy process so it becomes a two step thing (deploy DAC then deploy script). The positives of this are you can do things outside of the ssdt process but the negatives are you don't get things like auto disabling of constraints on tables changing in the deployment.

The second way is to not include the script in the deploy when you build but create an AfterBuild msbuild task that adds the script as a post deploy script in the dacpac. The dacpac is a zip file so you can use the .net packaging Api to add a part called postdeploy.sql which will then be included in the deployment process.

Both of these ways mean you lose verification so you might want to keep it in a separate ssdt project which has a "same database" reference to your main project, it will slow down the build when it changes but should be quick the rest of the time.