How to send a connection string as a parameter to MsBuild to perform SQL Schema Compare?

Question

The SQL Server Data Tools team blog mentions that it is now possible to use MsBuild to perform a schema comparison of two DacPacs or databases. However, they do not mention exactly how to pass in the connection string to the source and target database. If I set the parameter /p:source="my connection string" I get the error:

MSBUILD : error MSB4177: Invalid property. The name "Initial Catalog" contains an invalid character " ".

The command-line my PowerShell script sends to msbuild is:

msbuild ".\SchemaCompare.proj" /t:SqlSchemaCompare 
/p:source="$sourceConnString" /p:target="$targetConnString" 
/p:XmlOutput="$schemaCompareReportPath" 

where the SchemaCompare.proj contains the content suggested on the SQL Server Data Tools team blog

Piccaninny

Answer 1

It turns out that you have to replace the semicolons in the connection string with %3B, like so:

$sourceConnString = $sourceConnString.Replace(";", "%3B")

Explanation: This prevents delimiter collisions between the SQL Server ConnectionString value and the MSBuild /p (a.k.a. /properties) value that contains it. Absent this URL-encoding, MSBuild will tokenize the ConnectionString on semicolons rather than passing on the whole value intact (for the SQL Server client libraries to tokenize). This also applies to the Properties attribute of the MSBuild Task.

References:

• MSBuild Special Characters
• How to: Escape Special Characters in MSBuild

Macegan