如何在 sqlproj (SQL Server 2012) 脚本中使用 msbuild 属性 [英] how to use msbuild properties in sqlproj (SQL Server 2012) script

问题描述

我刚刚将现有的 SQL Server 2008 r2 .dbproj 升级为 SQL Server 2012 .sqlproj(使用 SQL Server Data Tools).

I just upgraded my existing SQL Server 2008 r2 .dbproj to a SQL Server 2012 .sqlproj (using SQL Server Data Tools).

以前，我能够在我的项目中定义一个 SQLCMD 变量，然后通过添加以下元素来编辑项目文件以使用 msbuild 值来定义该值:

Previously, I was able to define a SQLCMD variable in my project, and then define the value by editing the project file to use msbuild values by adding the following element:

<ItemGroup>
    <SqlCommandVariableOverride Include="ProjectDirectory=$(MSBuildProjectDirectory)" />
</ItemGroup>

然后我可以像这样在我的 PostDeployment 脚本中使用它:

Which I could then use in my PostDeployment script like this:

SELECT * INTO dbo.MyTable FROM dbo.MyTable WHERE 1=2
BULK INSERT dbo.MyTable
FROM  '$(ProjectDirectory)\data\dbo.MyTable.dat'
WITH (DATAFILETYPE = 'widenative')

但是，升级后，这似乎不再有效.

However, after the upgrade, this no longer seems to work.

我已经尝试将相同的条目添加到新的 sqlproj，但发布功能似乎没有选择它并希望我提供一个值.如果我提供 $(MSBuildProjectDirectory)，则按字面解释并失败.

I have tried adding that same entry to the new sqlproj, but the Publish functionality doesn't seem to pick it up and wants me to supply a value. If I supply $(MSBuildProjectDirectory), that is interpreted literally and fails.

在新制度下，指定本地文件路径和/或使用 msbuild 值的机制是什么?

Under the new regime, what is the mechanism for specifying a local filepath and/or using msbuild values?

推荐答案

在 sql server 2012 sqlproj(SSDT 数据库项目)中，您使用发布配置文件.您可以通过右键单击您的数据库项目并选择发布"来开始.

In a sql server 2012 sqlproj (SSDT database project) you use publishing profiles. You can start off by right-clicking your database project and choosing 'Publish'.

然后您可以设置所需的选项并将这些选项保存在项目中所谓的发布配置文件中.双击此配置文件会启动带有正确选项集的发布向导.

You can then set desired options and save these in a so-called publishing profile in your project. Double-clicking this profile launches the publishing wizard with the correct options set.

在您的发布配置文件中，您可以包含 sqlcmd 变量的硬编码值:

In your publish profile you can include hard-coded values for sqlcmd variables:

<ItemGroup>
    <SqlCmdVariable Include="ProjectDirectory">
        <Value>UNKNOWN</Value>
    </SqlCmdVariable>
</ItemGroup>

如果需要，您可以在构建期间使用动态值更新这些.在您的 msbuild 项目中:

If desired, you can update these with dynamic values during build. In your msbuild project:

<Target Name="SetProjectDirectoryInPublishXml">
    <ItemGroup>
        <Namespaces Include="nsMsbuild">
            <Prefix>nsMsbuild</Prefix>
            <Uri>http://schemas.microsoft.com/developer/msbuild/2003</Uri>
        </Namespaces>
    </ItemGroup>
    <ItemGroup>
        <SSDTPublishFiles Include="$(SolutionBinFolder)\**\*.publish.xml" />
    </ItemGroup>
    <MSBuild.ExtensionPack.Xml.XmlFile Condition="%(SSDTPublishFiles.Identity) != ''"
                                   TaskAction="UpdateElement"
                                   File="%(SSDTPublishFiles.Identity)"
                                   Namespaces="@(Namespaces)" 
                                   XPath="//nsMsbuild:SqlCmdVariable[@Include='ProjectDirectory']/nsMsbuild:Value" 
                                   InnerText="$(MSBuildProjectDirectory)"/>
</Target>

这需要一个扩展来更新 XML.我使用 msbuild 扩展包.

This requires an extension to update the XML. I use the msbuild extension pack.

此机制的学分转到 杰米汤姆森

这篇关于如何在 sqlproj (SQL Server 2012) 脚本中使用 msbuild 属性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！