Why Msbuild task failed to deploy database, but Exec work fine

Question

I'm trying to deploy database project ( dbproj format, not new SSDT sqlproj ) inside automated build server processing. I found the following:

When I'm calling deploy with Exec task in my Msbuild script - everything working fine:

<Exec Command="$(MSBuildPath)\MSBuild.exe $(SourceFilesPath)\$(DeployDatabaseProjectName)\$(DeployDatabaseProjectName).dbproj 
/t:Deploy 
/p:OutputPath=$(BaseOutput)\$(DeployDatabaseProjectName)\ 
/p:TargetDatabase=$(DeployDatabaseName)
/p:TargetConnectionString=$(DeployDatabaseConnectionString)" />

But when I try to repeat this with Msbuild task - it behaves differently:

<MSBuild Projects="$(SourceFilesPath)\$(DeployDatabaseProjectName)\$(DeployDatabaseProjectName).dbproj" 
            Targets="Deploy"
            Properties="Configuration=$(BuildConfiguration);
            TargetDatabase=$(DeployDatabaseName);
            TargetConnectionString=&quot;$(DeployDatabaseConnectionString)&quot;;
            OutputPath=$(BaseOutput)\$(DeployDatabaseProjectName)\;
            " />

Msbuild task broke on semicolons in DeployDatabaseConnectionString:

<DeployDatabaseConnectionString>Data Source=$(DeployDatabaseServer);Integrated Security=True;Pooling=False</DeployDatabaseConnectionString>

It will report something like this:

The name "Integrated Security" contains an invalid character " ".

But if I replace semicolons with percent encoding value - %3B - it will broke inside SqlDeployTask:

error MSB4018: The "SqlDeployTask" task failed unexpectedly.

What is the proper way to pass TargetConnectionString to Deploy target of SqlProject ?

PS: I Could live with exec task fine, but make a call to msbuild.exe inside msbuild script just hurts my inner perfectionist man.

Answer 1

I found the proper way - new Msbuild allow to define AdditionalProperties metadata on item. So with this feature everything work fine and have no problems with escaping\encoding

<ItemGroup>
    <DbProjectToBuild Include="$(SourceFilesPath)\$(DeployDatabaseProjectName)\$(DeployDatabaseProjectName).dbproj">
        <AdditionalProperties>Configuration=$(BuildConfiguration)</AdditionalProperties>
        <AdditionalProperties>OutputPath=$(BaseOutput)\$(DeployDatabaseProjectName)\</AdditionalProperties>
        <AdditionalProperties>TargetDatabase=$(DeployDatabaseName)</AdditionalProperties>
        <AdditionalProperties>TargetConnectionString="Data Source=$(DeployDatabaseServer);Integrated Security=True;Pooling=False"</AdditionalProperties>
    </DbProjectToBuild>
</ItemGroup>        
<MSBuild Projects="%(DbProjectToBuild.Identity)" Targets="Build;Deploy" />