如何将转换应用于 SSDT 发布配置文件 [英] How to Apply Transforms to SSDT publish profiles

问题描述

使用 Visual Studio 2013

Using Visual Studio 2013

我有一组 8 个 SSDT 项目，它们都可以部署到多个不同的环境中.但是，每个项目的高级发布设置都是相同的.目前，我为每个环境创建了一个不同的发布配置文件，这意味着我有大约 20 个发布配置文件，它们都使用完全相同的设置但不同的连接字符串.

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.

调整我的数据库的发布设置(这种情况经常发生，因为我对 SSDT 还是有点陌生​​)是最烦人的，但我还没有找到解决这个问题的方法，因为我无法应用转换来发布配置文件就像我可以在 ASP.NET 项目中的 web.config 文件一样.我什至尝试安装 Visual Studio SlowCheetah 插件，但它似乎不适用于 SSDT 项目，因为右键单击发布配置文件时不会出现应用转换的选项.

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.

我不希望我的团队在将数据库部署到开发或 QA 环境时考虑手动输入连接详细信息.有没有办法设置主发布配置文件或以其他方式指定一组共享设置，这样我就不必管理 20 个几乎相同的发布配置文件??

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??

使用 SAS 的回答，我能够为我的 .sqlproj 文件拼凑以下 XML:

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>

唯一的缺点是我的所有发布配置文件似乎都需要一个单独的文件夹，以防止一个转换覆盖另一个转换，我似乎找不到简单地覆盖现有文件的方法.对于 XmlPoke，命名空间属性对操作至关重要.我从 Sayed Ibrahim Hashimi 的这篇博文中了解了有关此过程的更多信息.

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.

推荐答案

对于我们的所有目标，我们正在使用自动复制的模板 xml 文件作为发布的前期步骤，因此任何更改都只需在模板.目标服务器名称在创建发布 xml 文件时动态替换.我们还必须为此修改 xaml.我们在常见的 proj 文件中使用 Copy 和 XMLPoke 标签，它们包含在我们的 proj 文件中.这需要一些工作，但效果很好.

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.

我在下面粘贴了一些代码来尝试解释，它只是原始代码的一部分，但我希望它足以让每个人开始:

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:

我们的公共文件 (SQLCommonInclude.proj) 中的这一部分:

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

这篇关于如何将转换应用于 SSDT 发布配置文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！