Include PostDeployment sql script in publish only in debug mode

Question

I have .dacpac project with Script.PostDeployment.sql:

:r .\SiteType.sql
:r .\Enums.sql
:r .\DebugData.sql

I want to include DebugData.sql conditionaly, only when i publish with DEBUG configuration profile selected in visual studio, so i will not have this data included when publish for production.

Is there any way to achieve it?

Anthracosilicosis

Answer 1

You can edit the .sqlproj file used by MSBuild that you use to create your dacpacs.

You can configure the .sqlproj so that certain scripts are included or not depending on what build configuration you are using.

Steps:

1. Create a project and add a PostDeployment script

2. Edit the .sqlproj file

3. Change the ItemGroup containing the PostDeploy script:

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

4. Build in Debug configuration. Extract the dacpac and you will find PostDeployment.sql

5. Build in Release configuration. Extract the dacpac and you will not find PostDeployment.sql.

Overside

Answer 2

One option is to run script every time at runtime, but skip its execution depending on server name. This will work if there is strict environment naming convention.

Inside DebugData.sql put the guard

-- here the condition is LIKE `DEV`, it could be NOT LIKE 'PROD'
IF @@SERVERNAME LIKE 'DEV%' 
BEGIN
    ... Here goes content of the script
END

---

Option two:

Instead of using single hardcoded value, prepare version for each configuration:

:r .\DebugData.sql
=>
:r ."\"$(Config)Data.sql

Now you need two files:

DebugData.sql    -- actual script
ReleasaeData.sql -- empty file

Depending of configuaration proile one of the files will be choosen. For release it is be the empty one, so no actual code will be executed.

Cheremkhovo