SQLPackage 部署后脚本未运行 [英] SQLPackage Post Deployment Script not running

问题描述

我在 SQL-Server 2012 上使用带有 TFS 的 Visual Studio 2013.具体来说，我在 VS 中使用 SQL-Server Data Tools 进行我们的 SQL 开发，而不是在 SQL-Server Management Studio 中.

I'm using Visual Studio 2013 with TFS, over SQL-Server 2012. Specifically I'm using SQL-Server Data Tools within VS for our SQL development, rather than within SQL-Server Management Studio.

简而言之，部署后 SQL 脚本似乎不会由 SQLPackage 执行.

In short, the Post-Deployment SQL script appears not to be executed by SQLPackage.

具体情况是:我有一个 VS SQL 数据库项目ADMIR"，它在过去几周一直在开发，并且在 SQL 及其部署方面逐渐变得更加复杂.该数据库包含一些动态生成的表，因此我引入了 SQL 命令变量来引用两个数据库(一个是实际的 ADMIR 数据库，另一个是同一服务器上的数据库).因此，我为这两个数据库添加了对 dacpacs 的数据库引用.

The specifics are: I have a VS SQL Database Project 'ADMIR', which has been developing over the last few weeks and gradually becoming more sophisticated both in terms of SQL and it's deployment. The database includes some dynamically generated tables and therefore I've introduced SQL Command Variables to reference two databases (one is the actual ADMIR database, the other a DB on the same Server). Therefore I've added Database References to dacpacs for these two DB's.

ADMIR 项目将顺利构建.

The ADMIR Project will Build fine.

还有一个 Post-Deployment SQL 脚本，在引入 SQL 命令变量之前，这也可以正常运行并初始化各种表数据.

There is also a Post-Deployment SQL script, and prior to the introduction of SQL Command Variables, this would also run fine and initialize various table data.

但是，我的理解是 SQL 命令变量不适用于 VS/TFS 发布操作，因为不再执行部署后脚本.

However, my understanding is that SQL Command Variables won't work with the VS/TFS Publish action, in that the Post-Deployment script is no longer being executed.

这没问题，因为最终我想编写/自动化 ADMIR 项目的发布/部署，所以我已经转而使用 SQLPackage 来执行发布.

This is ok, as ultimately I want to script/automate the Publish/Deploy of the ADMIR Project and so I've moved to using SQLPackage to perform the Publish.

同样，这似乎工作正常 - 我的 SQL 包批处理命令将执行并记录错误/输出(如适用)并将我的 Build dacpac 成功发布到目标服务器.但是，它似乎完全忽略了部署后脚本.此外，输出报告未设置 SQL 命令变量.

Again, this appears to work fine - My SQL Package batch command will execute and log errors/output (as applicable) and Publish my Build dacpac to the target Server successfully. However, it seems to completely ignore the Post-Deployment script. In addition, the output reports that the SQL Command Variables are not set.

这些是各种脚本和配置设置:

These are the various scripts and configuration settings:

SQLPackage 批处理命令:

::Publish the ADMIR database using the ADMIR.DEV.Publish.xml Profile.
"C:\Program Files (x86)\Microsoft SQL Server\120\DAC\bin\SqlPackage.exe" 
    /Action:Publish 
    /Profile:"C:\Source Control\ADMIR\ReleaseControl\ADMIR.DEV.publish.xml"
    /SourceFile:"C:\Source Control\ADMIR\Build\ADMIR.dacpac"
    2> "C:\Source Control\ADMIR\ReleaseControl\Publish_Error.txt" 
    > "C:\Source Control\ADMIR\ReleaseControl\Publish_Output.txt"

ADMIR.DEV.publish.xml 发布配置文件:

<?xml version="1.0" encoding="utf-8"?>
<Project ToolsVersion="12.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
  <PropertyGroup>
    <IncludeCompositeObjects>True</IncludeCompositeObjects>
    <TargetDatabaseName>ADMIR</TargetDatabaseName>
    <DeployScriptFileName>ADMIR.sql</DeployScriptFileName>
    <TargetConnectionString>Data Source=FOOBAR;Integrated Security=True;Pooling=False</TargetConnectionString>
    <ProfileVersionNumber>1</ProfileVersionNumber>
  </PropertyGroup>
  <ItemGroup>
    <SqlCmdVariable Include="ADMIRDatabaseName">
      <Value>ADMIR</Value>
    </SqlCmdVariable>
    <SqlCmdVariable Include="XYZDatabaseName">
      <Value>XYZDEV</Value>
    </SqlCmdVariable>
  </ItemGroup>
</Project>

Script.PostDeploy.sql(前几行):

/* ADMIR Post-Deployment Script Template */
PRINT N'Post Deploy Script started:'+CAST(GETDATE() AS NVARCHAR(20))+N'.'
PRINT N'SQLCMD Variable $ADMIRDBName:'+[$(ADMIRDatabaseName)]+N'.'
PRINT N'SQLCMD Variable $XYZDBName:'+[$(XYZDatabaseName)]+N'.'

/* Purge existing data as we'll repopulate with what is required for a fresh deploy */
PRINT N'Purging existing table data...'
DELETE FROM [ADMIR].[Engine].[ProcessingStatus]
DELETE FROM [ADMIR].[Engine].[DataField]
DELETE FROM [ADMIR].[Engine].[DataKeyField]
...
...

运行构建时，我可以看到 ADMIR_Create.sql 脚本确实包含所有部署后语句.即我可以在这个文件中看到上面的代码行.

When running a Build, I can see the ADMIR_Create.sql script does contain all the Post-Deploy statements. i.e. I can see the above lines of code in this file.

** ADMIR.sqlproj 文件包含部署后脚本和 SQL 命令变量:**

** ADMIR.sqlproj file contains the Post-Deploy scripts and the SQL Command Variables:**

<ItemGroup>
    ...
    ...
    <PostDeploy Include="ReleaseControl\Script.PostDeploy.sql" />
</ItemGroup>
...
...
<ItemGroup>
<SqlCmdVariable Include="ADMIRDatabaseName">
  <DefaultValue>ADMIR</DefaultValue>
  <Value>$(SqlCmdVar__7)</Value>
</SqlCmdVariable>
<SqlCmdVariable Include="XYZDatabaseName">
  <DefaultValue>XYZDEV</DefaultValue>
  <Value>$(SqlCmdVar__6)</Value>
</SqlCmdVariable>

发布日志，Publish_Output.txt:

Publishing to database 'ADMIR' on server 'FOOBAR'.
Initializing deployment (Start)
*** The following SqlCmd variables are not defined in the target scripts: ADMIRDatabaseName XYZDatabaseName.
Initializing deployment (Complete)
Analyzing deployment plan (Start)
Analyzing deployment plan (Complete)
Updating database (Start)
Update complete.
Updating database (Complete)
Successfully published database.

所以:

1. 为什么没有执行部署后脚本?我没有看到任何与其相关的日志信息.

1. Why is the Post-Deployment script not being executed? I'm not seeing any logging information relating to it.

当 SQL 命令变量位于发布配置文件中时，为什么 SQLPackage 输出日志指出未定义 SQL 命令变量?

Why does the SQLPackage output log state that the SQL Command Variables are not defined, when they are in the Publish profile?

可能与 2.sqlproj 文件是否正确，将 SQL 命令变量的值作为变量本身，例如$(SqlCmdVar__7)?其中 __6 &__7 来自?

Possibly related to 2. Is the .sqlproj file correct in have the values of the SQL Command Variables as variables themselves e.g. $(SqlCmdVar__7)? where __6 & __7 coming from?

感谢您的帮助！

推荐答案

我发现在 Visual Studio 2017 中创建的新 .sqlproj 文件不包含默认"Predeployment.sql 或 PostDeployment.sql 文件，所以我手动添加了一个.

I found that a new .sqlproj file created in Visual Studio 2017 did not include "default" Predeployment.sql or PostDeployment.sql files, so I added one manually.

发布未执行 PostDeployment.sql，因为文件的 Build Action 设置为 None.将 Build Action 更改为 PostDeploy 修复了该问题.

Publishing did not execute the PostDeployment.sql because the file's Build Action was set to None. Changing the Build Action to PostDeploy fixed the issue.

这篇关于SQLPackage 部署后脚本未运行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！