Conditionally build Post-Deploy in SSDT SQLPROJ

Question

I want to conditionally build the post-deploy script in my SSDT project, but i don't understand how to do that. So normally the post-deploy script is built, but I want a way to NOT build or run the post-deploy script when doing a Debug build. I am running the build from the command-line, so i could pass in properties, but how could i use a property to not run the post-deploy script?

The options i see are SQLCMD, or editing the SQLPROJ file, or passing in properties, but I can't find any reference for what the available properties are and what not for SQLPROJ files.

The file i want to conditionally build is located here:

<ItemGroup>
    <PostDeploy Include="PostDeploymentScripts\Script.PostDeployment1.sql" />
</ItemGroup>

My Debug build block looks like this:

  <PropertyGroup Condition=" '$(Configuration)|$(Platform)' == 'Debug|AnyCPU' ">
    <OutputPath>bin\Debug\</OutputPath>
    <BuildScriptName>$(MSBuildProjectName).sql</BuildScriptName>
    <TreatWarningsAsErrors>false</TreatWarningsAsErrors>
    <DebugSymbols>true</DebugSymbols>
    <DebugType>full</DebugType>
    <Optimize>false</Optimize>
    <DefineDebug>true</DefineDebug>
    <DefineTrace>true</DefineTrace>
    <ErrorReport>prompt</ErrorReport>
    <WarningLevel>4</WarningLevel>
  </PropertyGroup>

And my command-line looks like this:

msbuild $sqlprojFilePath /p:Configuration="Debug"

Nicodemeus · Accepted Answer · 2014-07-02T00:03:02.290

In your .sqlproj, add this line to occur after the import on Microsoft.Data.Tools.Schema.SqlTasks.targets

Find this line in your project file after the import on *SqlTasks.targets.

<Import Project="$(MSBuildExtensionsPath)\Microsoft\VisualStudio\v$(MSBuildToolsVersion)\SSDT\Microsoft.Data.Tools.Schema.SqlTasks.targets" />

<PropertyGroup Condition="'$(Configuration)'=='debug'">
  <DeployDependsOn />
  <SqlDeployDependsOn />
</PropertyGroup>

This removes the deployment projects from the dependency chain and can be modified to use an alternate property.

<PropertyGroup Condition="'$(SkipDeployment)'=='true'">
  <DeployDependsOn />
  <SqlDeployDependsOn />
</PropertyGroup>

Command line:

msbuild.exe mydb.sqlproj /p:SkipDeployment=true

Big Edit:

Or you can take this:

<ItemGroup>
    <PostDeploy Include="PostDeploymentScripts\Script.PostDeployment1.sql" />
</ItemGroup>

and change it to read like so:

<ItemGroup Condition="'$(Configuration)'=='debug'">
    <PostDeploy Include="PostDeploymentScripts\Script.PostDeployment1.sql" />
</ItemGroup>

Awesome! However, i can't find any info about the DeployDependsOn property. Do you have a link for documentation on that? I used your second idea here and it did exactly what I was wanting! — Tyler Jones, Jul 02 '14 at 01:00
Those are properties defined in Microsoft.Data.Tools.Schema.SqlTasks.targets which MsBuild uses to schedule/sequence dependent targets. By zeroing those values out the deploy section could be skipped, but even better that we just omit the post deploy script via a condition. — Nicodemeus, Jul 02 '14 at 18:22

score 1 · Answer 2 · answered Jul 01 '14 at 19:21

1

You can do this with a SQLCMD variable. Set one up in the project and check the value of that variable when publishing the project. I blogged about something like this here:

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

Not sure about the msbuild command-line as I've normally used sqlpackage.exe to push the changes.

answered Jul 01 '14 at 19:21

Peter Schott

4,521
21
30

Excellent blog post. This is a perfect solution except i'm needing to not BUILD the post-deploy. Once i've gotten to the sqlpackage.exe step, it's too late 'cause I've already had to build the SSDT project (creating the dacpac). I posted a question on your blog, however, about this approach regarding how to set the value of the SQLCMD variable... – Tyler Jones Jul 01 '14 at 20:26
I'll check out the question there in a little bit. I usually just let SSDT build the post-deploy script as if it's not the configuration I want according to the SQLCMD variable, it won't run. I also tend to use SQLPackage and Publish Profiles for setting those variables - much easier than trying to set a bunch of command-line params or even build config files to me. – Peter Schott Jul 02 '14 at 15:45

Conditionally build Post-Deploy in SSDT SQLPROJ

2 Answers2