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)Baseline\publish.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)Dev\publish.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.

Partlet

It's just an XML file behind the scenes - copy/paste the file, edit the connection portion, and include in project so it's visible. You should be able to right-click it and select "Open" as well to see the XML. It's definitely easy to copy/paste or just open existing one, change the connection string, and do a Save As. – Bunkmate

The publish profiles being XML files prompted me to try SlowCheetah . I was initially surprised that approach didn't work, but have since found an issue on the GitHub project that confirms SlowCheetah doesn't support SSDT (Issue 38 for the curious). I agree that copy pasting is easy, it is just time consuming when I have to manage so many. – Partlet

When I was creating these across a bunch of projects, I copied the base one using the template file into some standard matching that connect string. I then used Notepad++ to open all matching names and did a global replace in those files to change the connect strings and save them all. You could probably do that in PowerShell or something similar without too much trouble to parse that out and adjust connect strings. Adding to the project was a bit more time-consuming, but that was a one-time task for the projects. – Bunkmate

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)';

Dakar

I used your suggestions to come up with a rough solution which I have edited into the question. Is this close to what you were proposing? – Partlet

Looks like You got it! We use XMLPoke to also copy into some variables which are used in Extended Properties (ChangeSets, VersionNumber, and a few more). This is useful also to our develeopers, who can now easily see which version is deployed where. – Dakar

Also, we use a Target that is executed one for each server. I added some code in my answer above. – Dakar