ssis 包部署_如何使用各种选项从SSIS目录执行已部署的程序包

ssis 包部署

In my previous two articles on SQL Server integration Services (SSIS), Parameterizing Database Connection in SSIS and Deploying Packages to SSIS Catalog (SSISDB), packages were developed, deployed and configured in the SSIS Catalog. Now, it is time to execute the packages with various options. There are a couple of ways to do this, but we need to be able to change the parameter values as well as monitor for failures or successes.

在前两篇有关SQL Server集成服务（SSIS）， 在SSIS中参数化数据库连接和将程序包部署到SSIS目录（SSISDB）的文章中 ，已在SSIS目录中开发，部署和配置了程序包。 现在，是时候使用各种选项执行软件包了。 有两种方法可以执行此操作，但是我们需要能够更改参数值并监视失败或成功。

The most intuitive execution for a DBA would be to script the T-SQL to execute the package. When we do this, the package is run asynchronous, so it starts and returns quickly to the method used to execute. The T-SQL script uses a status to indicate if the package is executing, has succeeded or has failed along with various other values along the same line.

DBA最直观的执行是编写T-SQL脚本以执行程序包。 当我们这样做时，程序包将异步运行，因此它将启动并快速返回到用于执行的方法。 T-SQL脚本使用状态来指示程序包是正在执行，成功还是失败以及同一行上的各种其他值。

We can get this script by trying to execute a package deployed to the SSIS Catalog. If you right-click on the package from the SSIS Catalog, there will be a submenu called Execute… like Figure 1.

我们可以通过尝试执行部署到SSIS目录中的软件包来获取此脚本。 如果右键单击SSIS目录中的软件包，将有一个名为Execute的子菜单，如图1所示。

The Execute Package screen appears where you can change Project Parameter values. The values are retrieved based on the deployed values, assigned environment or the configured values after the project is deployed. Configuration of the changed values can come from one or more Environments that are assigned to the package within the deployed project.

出现执行程序包屏幕，您可以在其中更改项目参数值。 部署项目后，将根据部署的值，分配的环境或配置的值来检索这些值。 更改后的值的配置可以来自已部署项目中分配给程序包的一个或多个环境。

There is a Script button at the top of the execute screen where the text can be saved to a file, the clipboard or displayed in a new query window. Figure 3 shows the script in a new query window after some formatting changes.

执行屏幕顶部有一个脚本按钮，可将文本保存到文件，剪贴板或在新的查询窗口中显示。 图3展示了一些格式更改后在新查询窗口中的脚本。

The script starts with a declaration of the variable @execution_ID. This is the variable that will hold the status of the executing package while it is running asynchronous. Since no Project Parameters were changed, there is nothing declared or set for this script. The execution will use the SSIS Catalog configured values.

该脚本以变量@execution_ID的声明开始。 这个变量将在异步运行时保持正在执行的程序包的状态。 由于没有更改项目参数，因此没有为该脚本声明或设置任何内容。 执行将使用SSIS目录配置的值。

If the above script is executed, it will complete with no errors. The problem is only the SSIS Catalog logging will have the execution status for the running package. Nothing is returned to the execution of the script from the SSIS package. The value can be obtained by creating a loop to check the declared @execution_ID variable.

如果执行了上述脚本，它将完成且没有错误。 问题在于只有SSIS目录日志记录才会具有正在运行的程序包的执行状态。 SSIS包中的脚本执行不返回任何内容。 可以通过创建一个循环来检查声明的@execution_ID变量来获取该值。

The possible values are:

可能的值为：

1. running
   跑步
2. created
   被创造
3. canceled
   取消
4. failed
   失败了
5. pending
   待定
6. ended unexpectedly
   意外结束
7. succeeded
   成功了
8. stopping
   停止
9. completed
   已完成

The loop would need to check the value of @execution_ID and if it is 1, 2, 5 or 8, it is still running. Anything else would be completed. We would need to report a problem if the value ended up as 3, 4 or 6. Figure 4 shows the additional logic for looping to check the completion of the execution

循环将需要检查@execution_ID的值，如果它是1、2、5或8，它仍在运行。 其他任何事情都会完成。 如果该值最终为3、4或6，我们将需要报告一个问题。图4显示了用于循环执行以检查执行是否完成的其他逻辑

 
WHILE @execution_id IN (1,2,5,8)
	WAITFOR DELAY '00:01'
 
DECLARE @Msg VARCHAR(MAX)
DECLARE @MsgStatus VARCHAR(MAX)
SELECT @MsgStatus = CASE WHEN @execution_id IN (1,2,5,8) THEN 'failed' ELSE 'Succeeded' END
 
SET @Msg = 'Package DimCategory completed with a status of: ' 
    + @MsgStatus
 
PRINT @Msg
 

These packages were developed in a separate environment than production. So, when we deploy to a production server, the parameter values will be the same as development (or QA). The Environment of the SSIS Catalog is helpful with changing these deployed values. It is also helpful because the Project Parameters are used by multiple Packages in a Project. That is why the switch to Project Parameters is so important along with containing packages in a project deployed to the SSIS Catalog.

这些软件包是在与生产不同的环境中开发的。 因此，当我们部署到生产服务器时，参数值将与开发（或QA）相同。 SSIS目录的环境有助于更改这些部署的值。 这也很有用，因为项目参数由一个项目中的多个包使用。 这就是为什么切换到项目参数以及在包含部署到SSIS目录的项目中包含程序包时如此重要的原因。

Once deployed to the production server, we can create an Environment for the Project. Figure 5 shows where this is done.

部署到生产服务器后，我们可以为项目创建一个环境。 图5显示了这样做的位置。

First, the Environment has to be created with a name and description. Once that is done, the properties of the Environment can be edited. Figure 6 shows an Environment for changing the staging and production database names as well as the instance name.

首先，必须使用名称和描述创建环境。 一旦完成，就可以编辑环境的属性。 图6显示了一个用于更改登台数据库和生产数据库名称以及实例名称的环境。

Once this is save, we can relate an Environment to a Project. The Environment has to be created in the project’s Environment folder. The Project can be assigned values from the Environment variables to specific Project Parameters like Figure 7.

保存后，我们可以将环境与项目关联。 必须在项目的Environment文件夹中创建Environment。 可以从环境变量中为项目分配值到特定的项目参数，如图7所示。

This enables having the same deployed package run with different values for production versus QA. It also means the project and its packages only have to be deployed to production, if the developer feels ok with that. It does not prevent you from deploying to development, QA and/or production.

这样可以使相同部署的程序包以不同的值运行以进行生产和质量检查。 这也意味着如果开发人员对此感到满意，则仅需将该项目及其软件包部署到生产中。 它不会阻止您进行开发，质量检查和/或生产。

You can also setup SQL Server Agent jobs to run with different Environments. Figure 8 shows an agent job with an Environment assigned. The variable names created in the Environment must match the Project Parameter names for this to work in the SQL Server Agent job.

您还可以设置SQL Server代理作业以在不同的环境中运行。 图8显示了分配了环境的代理作业。 在环境中创建的变量名称必须与项目参数名称匹配，才能在SQL Server代理作业中工作。

The step of a SQL Server Agent job can have a text file log the information about a success or failure. If you go to the Advanced option in the top left of the Job Step properties, there is an Output file text box and ellipse where you can find a path and enter a filename. Figure 9 shows an option to write the output of the step to a specific location. There is also an option to ‘Append step output to existing file’. If not checked, the file is overwritten when the step is run. Not checking this box appends the output to the same file keeping the history of executions. If you use the append option, the file will grow larger with each run, so be sure to check the size and purge frequently.

SQL Server代理作业的步骤可以使文本文件记录有关成功或失败的信息。 如果转到“作业步骤”属性左上方的“高级”选项，则会有一个“输出文件”文本框和椭圆形，您可以在其中找到路径并输入文件名。 图9显示了将步骤的输出写入特定位置的选项。 还有一个选项“将步骤输出附加到现有文件”。 如果未选中，则在运行步骤时文件将被覆盖。 不选中此复选框会将输出追加到保留执行历史的同一文件中。 如果使用append选项，则每次运行时文件都会变大，因此请确保检查大小并经常清除。

Even though we started with scripting T-SQL to launch a package, we saw that assigning the Project an Environment will override the deployed values for Project Parameter(s). The Environment can also be related to an SSIS package as a step in the SQL Server Agent. The Step has a way to log information related to execution and errors.

即使我们开始使用脚本编写T-SQL来启动程序包，我们仍然看到为项目分配环境将覆盖项目参数的已部署值。 在SQL Server代理中，环境还可以与SSIS包相关。 该步骤可以记录与执行和错误相关的信息。

Side Note

边注

The asynchronous execution can be changed to run synchronous. The syntax below accomplishes making the package run synchronous from a T-SQL script.

可以将异步执行更改为同步运行。 下面的语法可以使程序包从T-SQL脚本同步运行。

 
  EXEC [SSISDB].[catalog].[set_execution_parameter_value] 
    @execution_id,  
	@object_type=50, 
	@parameter_name=N'SYNCHRONIZED', 
	@parameter_value=1
 

The parameter value change would make the execution of this package through T-SQL wait to return to caller once the execution finishes. This includes if it fails.

参数值更改将使该包通过T-SQL的执行一旦执行完成就等待返回给调用方。 这包括是否失败。

参考链接： ( Reference links: )

• catalog.create_execution (SSISDB Database) catalog.create_execution（SSISDB数据库）
• Job Step Properties – New Job Step (Advanced Page) 作业步骤属性–新作业步骤（高级页面）
• SSISDB Project Environments SSISDB项目环境

翻译自: https://www.sqlshack.com/execute-deployed-package-ssis-catalog-various-options/

ssis 包部署