SSDT Publish does not use connection string from MSBuild command line

Question

I'm trying to publish a .sqlproj project using MSBuild from my (TeamCity) build server. I use a .publish.xml for setting up most of the publish settings, but want to specify the connection string on the command line, so I can securely store it in TeamCity properties.

However, it seems like MSBuild doesn't use the connection string specified on the command line at all, and insists on using the one in the .publish.xml. I've tried removing it from and emptying it in the XML, but MSBuild doesn't like that so much, and spits back Before you can deploy a database, you must specify a connection string in the project properties or at a command prompt., which, of course, is exactly what I'm trying to do...

The full command used to publish is as follows:

msbuild /t:Build;Publish /p:SqlPublishProfilePath="c:\path\to\profile.publish.xml" /p:Publish_TargetConnectionString="the connection string" "c:\path\to\project.sqlproj"

I've tried using /p:TargetConnectionString and /p:SqlPublishProfilePath="c:\path\to\profile.publish.xml";Publish_TargetConnectionString="the connection string" as well, but to no avail.

The .publish.xml is as follows:

<?xml version="1.0" encoding="utf-8"?>
<Project ToolsVersion="12.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
  <PropertyGroup>
    <IncludeCompositeObjects>True</IncludeCompositeObjects>
    <TargetDatabaseName><!-- snip --></TargetDatabaseName>
    <DeployScriptFileName><!-- snip -->.sql</DeployScriptFileName>
    <TargetConnectionString><!-- snip snip --></TargetConnectionString>
    <ScriptDatabaseOptions>False</ScriptDatabaseOptions>
    <BlockOnPossibleDataLoss>False</BlockOnPossibleDataLoss>
    <DeployDatabaseInSingleUserMode>False</DeployDatabaseInSingleUserMode>
    <ProfileVersionNumber>1</ProfileVersionNumber>
  </PropertyGroup>
</Project>

SSDT is at the latest version I could download from Microsoft as of today.

Has anyone ever gotten this to work?

I have had different issues with the msbuild task it might just be me though. What I do is use msbuild to call sqlpackage.exe directly as you have much more control and it seems to work better — Ed Elliott

MLowijs MLowijs · Accepted Answer · 2015-07-14T13:38:29

As said above in the comments, I resorted to using MSBuild for building the .dacpac and sqlpackage.exe to publish it to SQL Server, and that works fine.

SSDT Publish does not use connection string from MSBuild command line

1 Answers