如何在ssdt后部署脚本中有条件地包含大型脚本?

In our SSDT project we have a script that is huge and contains a lot of INSERT statements for importing data from an old system. Using sqlcmd variables, I'd like to be able to conditionally include the file into the post deployment script.

在我们的SSDT项目中，我们有一个非常大的脚本，它包含许多插入语句，用于从旧系统导入数据。使用sqlcmd变量，我希望能够有条件地将文件包含到后部署脚本中。

We're currently using the :r syntax which includes the script inline:

我们目前使用的是:r语法，其中包括内联脚本:

IF '$(ImportData)' = 'true'
BEGIN
  :r .\Import\OldSystem.sql
END

This is a problem because the script is being included inline regardless of whether $(ImportData) is true or false and the file is so big that it's slowing the build down by about 15 minutes.

这是一个问题，因为无论$(ImportData)是true还是false，脚本都被包含在内联中，而且文件太大了，以至于使构建速度减慢了大约15分钟。

Is there another way to conditionally include this script file so it doesn't slow down the build?

是否有另一种方法有条件地包含这个脚本文件以使其不会减慢构建?

4 个解决方案

#1

Rather than muddy up my prior answer with another. There is a special case with a VERY simple option.

而不是把我先前的回答弄得乱七八糟。有一个特殊的情况，有一个非常简单的选择。

Create separate SQLCMD input files for each execution possibility. The key here is to name the execution input files using the value of your control variable.

为每个执行可能性创建单独的SQLCMD输入文件。这里的关键是使用控件变量的值来命名执行输入文件。

So, for example, your publish script defines variable 'Config' which may have one of these values: 'Dev','QA', or 'Prod'.

因此，例如，您的发布脚本定义了变量'Config'，它可能具有以下值之一:'Dev'、'QA'或'Prod'。

Create 3 post deployment scripts named 'DevPostDeploy.sql', 'QAPostDeploy.sql' and 'ProdPostDeploy.sql'.

创建名为“DevPostDeploy”的3个后部署脚本。sql”、“QAPostDeploy。sql”和“ProdPostDeploy.sql”。

Code your actual post deploy file like this:

将实际的post部署文件编码如下:

:r ."\"$(Config)PostDeploy.sql

:r。“\”(配置)PostDeploy.sql美元

This is very much like the build event mechanism where you overwrite scripts with appropriate ones except you don't need a build event. But you are dependent upon naming your scripts very specifically.

这非常类似于构建事件机制，除了不需要构建事件之外，您可以使用适当的脚本覆盖脚本。但是您需要非常具体地命名脚本。

#2

The scripts referenced using :r are always included. You have a couple of options but I would first verify that if you take the script out it improves the performance to where you want it to get to.

使用:r引用的脚本总是包含在内。您有几个选项，但我首先要验证的是，如果将脚本取出，它将把性能提高到您希望的位置。

The simplest approach is to just keep it outside of the whole build process and change your deploy process so it becomes a two step thing (deploy DAC then deploy script). The positives of this are you can do things outside of the ssdt process but the negatives are you don't get things like auto disabling of constraints on tables changing in the deployment.

最简单的方法是将其放在整个构建过程之外，并更改部署过程，使其成为两步操作(部署DAC然后部署脚本)。这样做的好处是你可以在ssdt过程之外做一些事情，但缺点是你不能自动禁用在部署过程中改变的表的约束。

The second way is to not include the script in the deploy when you build but create an AfterBuild msbuild task that adds the script as a post deploy script in the dacpac. The dacpac is a zip file so you can use the .net packaging Api to add a part called postdeploy.sql which will then be included in the deployment process.

第二种方法是在构建时不将脚本包含在部署中，而是创建一个AfterBuild msbuild任务，该任务将该脚本作为后部署脚本添加到dacpac中。dacpac是一个zip文件，因此您可以使用。net打包Api来添加称为postdeploy的部分。然后将包含在部署过程中的sql。

Both of these ways mean you lose verification so you might want to keep it in a separate ssdt project which has a "same database" reference to your main project, it will slow down the build when it changes but should be quick the rest of the time.

这两种方法都意味着您将失去验证，因此您可能希望将它保存在一个单独的ssdt项目中，该项目对您的主项目具有“相同的数据库”引用，当它发生更改时，它将减慢构建，但是在其他时间应该很快。

#3

Here is the way I had to do it.

这就是我要做的。

1) Create a dummy post-deploy script.

1)创建一个虚拟的后部署脚本。

2) Create build configurations in your project for each deploy scenario.

2)为每个部署场景在项目中创建构建配置。

3) Use a pre-build event to determine which post deploy configuration to use. You can either create separate scripts for each configuration or dynamically build the post-deploy script in your pre-build event. Either way you base what you do on the value of $(configuration) which always exists in a build event.

3)使用预构建事件来确定要使用哪个后部署配置。您可以为每个配置创建单独的脚本，也可以在预构建事件中动态构建部署后脚本。无论哪种方式，您都是基于在构建事件中始终存在的$(配置)的值。

If you use separate static scripts, your build event only needs to copy the appropriate static file, overwriting the dummy post-deploy with whichever script is useful in that deploy scenario.

如果您使用独立的静态脚本，那么构建事件只需要复制适当的静态文件，使用在该部署场景中有用的任何脚本覆盖虚拟的后部署。

In my case I had to use dynamic generation because the decision about which scripts to include required knowing the current state of the database being deployed to. So I used the configuration variable to tell me which environment was being deployed to and then used an SQLCMD script with :OUT set to my Post-Deploy script location. Thus my pre-build script would then write the post-deploy script dynamically.

在我的例子中，我必须使用动态生成，因为在决定要包含哪些脚本时，需要知道正在部署的数据库的当前状态。因此，我使用configuration变量告诉我要部署到哪个环境，然后使用SQLCMD脚本:OUT设置到我的后部署脚本位置。因此，我的预构建脚本将会动态地编写部署后脚本。

Either way, once build completed and the normal deploy process started the Post-Deploy script contained exactly the :r commands that I wanted.

无论哪种方式，构建完成后，正常部署过程启动后，部署脚本包含我想要的:r命令。

Here's an example of the SQLCMD script I invoke in pre-build.

下面是我在预构建中调用的SQLCMD脚本的示例。

:OUT .\Script.DynamicPostDeployment.sql

PRINT ' /*';
PRINT '     DO NOT MANUALLY MODIFY THIS SCRIPT.                                               ';
PRINT '                                                                                       ';
PRINT '     It is overwritten during build.                                                   ';
PRINT '     Content IS based on the Configuration variable (Debug, Dev, Sit, UAT, Release...) ';
PRINT '                                                                                       ';
PRINT '     Modify Script.PostDeployment.sql to effect changes in executable content.         ';
PRINT ' */';
PRINT 'PRINT ''PostDeployment script starting at''+CAST(GETDATE() AS nvarchar)+'' with Configuration = $(Configuration)'';';
PRINT 'GO';
IF '$(Configuration)' IN ('Debug','Dev','Sit')
BEGIN
    IF (SELECT IsNeeded FROM rESxStage.StageRebuildNeeded)=1
    BEGIN
        -- These get a GO statement after every file because most are really HUGE
        PRINT 'PRINT ''ETL data was needed and started at''+CAST(GETDATE() AS nvarchar);';
        PRINT '                                                  ';
        PRINT 'EXEC iESxETL.DeleteAllSchemaData ''pExternalETL'';';
        PRINT 'GO';
        PRINT ':r .\PopulateExternalData.sql         ';
....

#4

I ended up using a mixture of our build tool (Jenkins) and SSDT to accomplish this. This is what I did:

我最终使用了我们的构建工具(Jenkins)和SSDT的混合来实现这一点。这就是我所做的:

Added a build step to each environment-specific Jenkins job that writes to a text file. I either write a SQLCMD command that includes the import file or else I leave it blank depending on the build parameters the user chooses.
向每个特定于环境的Jenkins作业添加一个构建步骤，该任务将写入文本文件。我要么编写包含导入文件的SQLCMD命令，要么根据用户选择的构建参数将其留空。
Include the new text file in the Post Deployment script via :r.
通过:r在后部署脚本中包含新的文本文件。

That's it! I also use this same approach to choose which pre and post deploy scripts to include in the project based on the application version, except that I grab the version number from the code and write it to the file using a pre-build event in VS instead of in the build tool. (I also added the text file name to .gitignore so it doesn't get committed)

就是这样!我也用同样的方法来挑选前后部署脚本中包含项目的基础上,应用程序版本,除了我抓起代码的版本号,写文件使用预构建事件在VS而不是构建工具。(我还将文本文件名添加到.gitignore，这样就不会提交)

#1