sql server data tools - How to Apply Transforms to SSDT publish profiles

Question

Using Visual Studio 2013

I have a set of 8 SSDT projects that can all be deployed to several distinct environments. The advanced publish settings for each project, however, are meant to be identical. At present I have created a distinct publish profile for each environment, meaning I have ~20 publish profiles all using the exact same settings but different connection strings.

Tweaking the publish settings (which happens with some regularity as I am still a bit new to SSDT) for my databases is most annoying, but I have yet to find a way around this as I am unable to apply transforms to publish profiles like I can to web.config files in an ASP.NET project. I even tried installing the Visual Studio SlowCheetah plugin, but it doesn't appear to work with SSDT projects as the option to apply transform does not appear when right-clicking on a publish profile.

I don't want my team to have to think about entering connection details manually when deploying a DB to dev or QA environments. Is there any way to set a master publish profile or otherwise specify a group of shared settings so that I don't have to manage 20 nearly identical publish profiles??

EDIT: Using SAS' answer I was able to cobble together the following XML for my .sqlproj file:

  <PropertyGroup>
    <PublishProfileDir>$(ProjectDir)Publish Profiles</PublishProfileDir>
    <TemplatePublishProfile>$(PublishProfileDir)Baselinepublish.xml</TemplatePublishProfile>
  </PropertyGroup>
  <Target Name="CopyXml" AfterTargets="Build">
    <Copy SourceFiles="$(TemplatePublishProfile)" DestinationFolder="$(PublishProfileDir)Dev"/>
    <Copy SourceFiles="$(TemplatePublishProfile)" DestinationFolder="$(PublishProfileDir)Qa"/>
  </Target>
  <ItemGroup>
    <DevPublishUpdates Include="ConfigUpdates">
      <XPath>/msb:Project/msb:PropertyGroup/msb:TargetDatabaseName</XPath>
      <NewValue>CountyRecordsDev</NewValue>
    </DevPublishUpdates>
    <DevPublishUpdates Include="ConfigUpdates">
      <XPath>/msb:Project/msb:PropertyGroup/msb:DeployScriptFileName</XPath>
      <NewValue>CountyRecords.Dev.Sql</NewValue>
    </DevPublishUpdates>
  </ItemGroup>
  <Target Name="UpdateXml" AfterTargets="CopyXml">
    <Message Text="Editing Derived Xml Publish Profiles" Importance="high" />
    <XmlPoke Namespaces="&lt;NamespacePrefix='msb'Uri='http://schemas.microsoft.com/developer/msbuild/2003'/&gt;"
        XmlInputath="$(PublishProfileDir)Devpublish.xml" 
        Query="%(DevPublishUpdates.XPath)" 
        Value="%(DevPublishUpdates.NewValue)" />
  </Target>

The only downside is that I seem to need a separate folder for all my publish profiles in order to prevent one transform from overwriting another, I could not seem to find a way to simply overwrite an existing file. For XmlPoke, the namespaces attribute is critical to operation. I learned more about this process from this blog post by Sayed Ibrahim Hashimi.

Answer 1

We are using a template xml file that is copied automagically as a pre-step in the publish, for all our targets, so any changes need only be mande in the template. The target server name is replaced dynamically as the publish xml files are created. We also had to modify the xaml for this. We use Copy and XMLPoke tags in common proj-file thar is included in our proj-files. It takes some work, but works fine.

Edit: I have pasted in some code below to try to explain, it is only part of the original but I hope it is enough to get everyone started:

This part of what is in our common file (SQLCommonInclude.proj):

<Target Name="CreatePublishXMLFile">
 <PropertyGroup>
  <VersionNumber Condition="'$(VersionNumber)'==''">Local Build</VersionNumber>
  <CurrentDate>$([System.DateTime]::Now.ToString(yyyy-MM-dd HH:mm:ss))</CurrentDate>
  <SqlPublishProfilePath Condition="'$(SqlPublishProfilePath)'==''">Publish$(TargetServerParam).publish.xml</SqlPublishProfilePath>
  <TargetXMLFile>$(ProjectDir)Publish$(TargetServerParam).publish.xml</TargetXMLFile>
  <ChangeSets Condition="'$(ChangeSets)'==''">Unknown</ChangeSets>
 </PropertyGroup>
 <XmlPoke XmlInputPath="$(TargetXMLFile)" Query="/*[local-name()='Project']/*[local-name()='PropertyGroup']/*[local-name()='TargetConnectionString']" Value="Data Source=$(TargetServerParam)%3BIntegrated Security=True%3BPooling=False" />
 <XmlPoke XmlInputPath="$(TargetXMLFile)" Query="/*[local-name()='Project']/*[local-name()='PropertyGroup']/*[local-name()='TargetDatabaseName']" Value="$(ProjectName)" />
 <XmlPoke XmlInputPath="$(TargetXMLFile)" Query="/*[local-name()='Project']/*[local-name()='ItemGroup']/*[local-name()='SqlCmdVariable'][@Include='ChangeSets']/*[local-name()='Value']" Value="$(ChangeSets)" />
</Target>

Then call this repeatedly, for each target server:

<Target Name="CreateAllPublishXMLFiles">
 <MSBuild Projects="$(MSBuildProjectFile)" Targets="CreatePublishXMLFile" Properties="TargetServerParam=OURSERVER1" />
 <MSBuild Projects="$(MSBuildProjectFile)" Targets="CreatePublishXMLFile" Properties="TargetServerParam=OURSERVER2" />
</Target>

In each Project file we include and call the common code:

<Import Project="$(SolutionDir)SQLCommonInclude.proj" />
<Target Name="BeforeBuild" DependsOnTargets="CreateAllPublishXMLFiles">

Then, In a Post-deployment Script we set the Extended Properties like this:

IF NOT EXISTS (SELECT NULL FROM SYS.EXTENDED_PROPERTIES WHERE class_desc = 'DATABASE' AND name = 'SSDT ChangeSets')
EXEC sp_addextendedproperty @name = N'SSDT ChangeSets', @value = '';
EXEC sp_updateextendedproperty @name = N'SSDT ChangeSets', @value =  '$(ChangeSets)';