ssis sql_如何设置SQL代理作业警报以包括SSIS目录错误

culuo4781

于 2020-07-22 08:19:26 发布

阅读量1.1k

点赞数

文章标签：数据库 java mysql python linux

原文链接：https://www.sqlshack.com/setup-sql-agent-job-alerts-include-ssis-catalogue-errors/

版权

ssis sql

I must have been one of the first people who abandoned BIDS as soon as SSDT was first introduced. Although I have never regretted that decision, I do sometimes feel that SSDT has introduced an unnecessary additional layer of troubleshooting package execution failures. Regardless of whether your SSIS packages are deployed using BIDS or SSDT, the common scheduling mechanism used to run those packages is usually the SQL Server Agent. This is where you are likely to encounter additional layers of troubleshooting SSDT-deployed packages. This is because unlike BIDS where package execution details were stored in the same database (msdb) as were the execution details of SQL Server Agent job, the advent of SSDT came with an introduction a new database – SSISDB – which is used to, amongst other things, store package execution details. Subsequently, in order to retrieve details of package execution, we ought to query the SSIDB database.

我一定是最早在SSDT引入后放弃BIDS的第一批人之一。尽管我从未后悔过这个决定，但有时我确实会感到SSDT引入了不必要的额外层来排除软件包执行故障。无论使用BIDS还是SSDT部署SSIS软件包，用于运行这些软件包的通用调度机制通常是SQL Server代理。在这里，您可能会遇到对SSDT部署的软件包进行故障排除的其他层。这是因为与BIDS不同，在BIDS中，包执行详细信息与SQL Server Agent作业的执行详细信息存储在同一数据库（msdb）中，SSDT的问世是引入了一个新数据库SSISDB，该数据库用于除其他外。东西，存储包执行细节。随后，为了检索包执行的详细信息，我们应该查询SSIDB数据库。

To illustrate such an unnecessary inconvenience, let’s say I have an SSIS package – PackageTest.dtsx – that is running inside a SQL Server Agent job and makes use of an Execute SQL Task to run a T-SQL code depicted in Script 1:

为了说明这种不必要的不便，假设我有一个SSIS包PackageTest.dtsx ，它在SQL Server代理作业中运行，并利用Execute SQL Task运行脚本1中描述的T-SQL代码：

 
SELECT 1'

Script 1: T-SQL statement with incorrect syntax

脚本1：语法错误的T-SQL语句

If I deploy this package via BIDS and then execute it using a fictitious SQL Server Agent job, the package execution fails as expected (due to an unclosed quotation mark) and the details of the failure are provided in the SQL Server Agent job history log, as shown in Figure 1.

如果我通过BIDS部署了此程序包，然后使用虚拟SQL Server代理作业执行了该程序包，则该程序包的执行将按预期失败（由于未加上引号），并且在SQL Server代理作业历史记录日志中提供了失败的详细信息， 如图1所示。

Then, it is easy for me to set up a custom notification service (using an msdb.dbo.sp_send_dbmail system stored procedure) that will, upon job failure, iterate through a given job’s history and send out emails to inform and provide support developers the details of the package error, as per the sample email shown in Figure 2.

然后，对于我来说，很容易设置一个自定义通知服务（使用msdb.dbo.sp_send_dbmail系统存储过程），该服务将在作业失败时遍历给定作业的历史记录，并发送电子邮件以通知并向开发人员提供支持包错误的详细信息， 如图2所示的示例电子邮件。

Unfortunately, replicating the same notification process against SSDT deployed packages is not so straightforward. Say for instance I deploy the same package I mentioned above but this time around I deploy it into the Integration Services Catalog using SSDT, and run it within a SQL Server Agent job. Well, although the definition of the package is still the same, I am now getting a different error message in SQL Agent job, as shown in Figure 3.

不幸的是，对部署了SSDT的软件包复制相同的通知过程并不是那么简单。举例来说，我部署了与我上面提到的相同的程序包，但是这次我使用SSDT将其部署到Integration Services目录中，并在SQL Server代理作业中运行它。很好，尽管软件包的定义仍然相同，但是现在我在SQL Agent作业中得到了不同的错误消息， 如图3所示。

In fact, compared to the error message logged in Figure 1, the error message logged in Figure 3 is shorter as I am advised to rather look for details of the package execution under Integration Services Catalog’s All Executions report. This means that I may now have to grant whoever will be troubleshooting this error (it could be consultants or part-time staff sitting around the world), access to the production instance of the SSISDB in order to view package execution details.

实际上，与图1中记录的错误消息相比， 图3中记录的错误消息要短一些，因为建议我在Integration Services目录的“ 所有执行”报告下查找有关包执行的详细信息。这意味着我现在可能不得不授予要解决此错误的人员（可以是遍布世界各地的顾问或兼职人员），可以访问SSISDB的生产实例，以便查看程序包执行详细信息。

如何检索SSIS目录错误消息 (How to retrieve SSIS catalog error messages)

When dealing with Integration Services Catalog based packages, we clearly need to introduce a custom SQL Server Agent job step that will retrieve details of the package execution error and send them out to the relevant recipients – without having people go through the All Executions report!

在处理基于Integration Services Catalog的程序包时，我们显然需要引入一个自定义SQL Server代理作业步骤，该步骤将检索程序包执行错误的详细信息并将其发送给相关的收件人，而无需人们查看“ 所有执行”报告！

Get Latest Execution ID
获取最新执行ID

Every Integration Services Catalog execution is assigned an Execution ID. This Execution ID can be retrieved from the [SSISDB].[internal].[executions] table. Because this table stores executions for all packages, we need filter it either by a package name or a project name. The complete script on how to retrieve the latest Execution ID is given in Script 2.

每个Integration Services Catalog执行都分配了一个执行ID 。可以从[SSISDB]。[内部]。[执行]表中检索此执行ID 。由于此表存储了所有软件包的执行情况，因此我们需要按软件包名称或项目名称对其进行过滤。如何获取最新的执行ID完整的脚本在脚本2中给出。

 
 SELECT MAX([execution_id]) [execution_id] 
	FROM [SSISDB].[internal].[executions] (NOLOCK) 
	WHERE [package_name] = 'PackageTest.dtsx'

Script 2: Retrieve latest Execution ID

脚本2：检索最新的执行ID

Retrieve Error Messages
检索错误消息

With the latest Execution ID in hand, we now need to retrieve all error messages relating to that latest Execution ID. There are two options we can go about doing this; one would involve joining several tables including [SSISDB]. [internal].[operation_messages] whilst another option involves querying a built-in Integration Services Catalog view –[SSISDB].[catalog].[event_messages], in this discussion we have opted to query the view.

有了最新的执行ID ，我们现在需要检索与该最新执行ID相关的所有错误消息。我们有两种选择可以做到这一点；一个将涉及联接包括[SSISDB]在内的几个表。 [internal]。[operation_messages]，而另一个选项涉及查询内置的Integration Services目录视图– [SSISDB]。[catalog]。[event_messages] ，在此讨论中，我们选择了查询视图。

Similarly to the executions table, the [SSISDB].[catalog].[event_messages] table contains all messages relating Integration Services Catalog events and executions. Therefore we should rather reduce the dataset from this view by applying two filters; the Execution ID and OnError event type. The complete script that retrieves package execution messages is shown in Script 3:

与执行表相似， [SSISDB]。[catalog]。[event_messages]表包含与Integration Services Catalog事件和执行有关的所有消息。因此，我们应该通过应用两个过滤器来减少此视图中的数据集。 执行ID和OnError事件类型。 脚本3中显示了检索包执行消息的完整脚本：

 
SELECT  
event_message_id
, event_name
, message_time
, [message]
, message_source_name
, execution_path
FROM    [SSISDB].[catalog].[event_messages] em
WHERE   em.operation_id = (
    SELECT MAX([execution_id]) 
    FROM [SSISDB].[internal].[executions] (nolock) 
    where [package_name] = 'PackageTest.dtsx'
)
  AND event_name NOT LIKE '%Validate%'
  AND event_name = 'OnError'

Script 3

脚本3

A preview of the results of Script 3 execution are shown in Figure 4 and you would notice that they closely resemble details provided in the All Executions report shown in Figure 5.

脚本3执行结果的预览如图4所示，您会注意到它们与图5所示的“ 所有执行”报告中提供的细节非常相似。

配置错误通知作业步骤 (Configure error notification Job step)

Now that we have retrieved the error messages, we can proceed to customize our error SQL Server Agent job to include a job step that will send out the error messages to our support developers/DBA. The simplest way of implementing such a step is to have it execute a stored procedure.

现在，我们已经检索了错误消息，我们可以继续自定义错误SQL Server代理作业，以包含一个作业步骤，该步骤会将错误消息发送给我们的支持开发人员/ DBA。实现此步骤的最简单方法是使其执行存储过程。

The definition of the stored procedure is too long to provide in this article instead I have included a download location under the Downloads location at the bottom of this article. Nevertheless, looking at the preview of the spSendEmailISCatalog stored procedure shown in Figure 6, we can see that it only requires two parameters; email addresses of recipients as well as an email subject line. However, feel free to customize the stored procedure according to your preference.

存储过程的定义太长此文章我已经包含在本文底部的下载位置，下一个下载位置中提供。尽管如此，查看图6所示的spSendEmailISCatalog存储过程的预览，我们可以看到它仅需要两个参数；即，它仅需要两个参数。收件人的电子邮件地址以及电子邮件主题行。但是，可以根据自己的喜好自定义存储过程。

Figure 7 shows an updated job step list of our sample job that now includes the send out error email job step name.

图7显示了我们的示例作业的更新的作业步骤列表，现在包括发出错误电子邮件的作业步骤名称。

Following the changes to the job, Figure 8 shows an email alert that now successfully includes the error details of an SSDT-deployed package.

更改作业后，图8显示了一封电子邮件警报，该警报现已成功包含SSDT部署的软件包的错误详细信息。

SSIS报告包 (SSIS Reporting pack)

Jamie Thomson, one of leading bloggers on topics relating to SSIS, has a free-to-download SSIS Reporting Pack that contains a list of SSRS reports that are similar to the Integration Services Catalog’s built-in reports – including the All Executions report. Thus, instead of introducing a custom error notification job step in all your jobs as covered in this article, you could also just restore the SSRS reports found in the SSIS Reporting Pack and have people go through those reports instead of granting them access to reports available from the Integration Services Catalog dashboard. You will still need an account that has access to the SSISDB to run those reports.

Jamie Thomson是与SSIS相关的主题的领先博客之一，拥有一个免费下载的SSIS报告包 ，其中包含与Integration Services Catalog的内置报告（包括“ 所有执行”报告）相似的SSRS报告列表。因此，除了在本文中介绍的所有作业中引入自定义错误通知作业步骤之外，您还可以仅还原在SSIS报告包中找到的SSRS报告并让人们查看这些报告，而不是授予他们访问Integration Services目录仪表板中可用报告的权限。您仍然需要一个有权访问SSISDB的帐户来运行那些报告。

结论 (Conclusion)

In this article, we have covered different logging by SQL Server Agent jobs of error messages pertaining to package execution failures. It was illustrated how SQL Server Agent job’s error reporting of BIDS-deployed packages is usually more verbose compared to SSDT-deployed packages. Finally, an alternative custom error notification job step using a stored procedure that queries SSISDB catalog views and tables was provided.

在本文中，我们讨论了与包执行失败有关的错误消息SQL Server代理作业的不同日志记录。它说明了与SSDT部署的软件包相比，SQL Server Agent作业对BIDS部署的软件包的错误报告通常更详细。最后，提供了使用查询SSISDB目录视图和表的存储过程的替代自定义错误通知作业步骤。