如何在 SSIS 包中创建错误日志或自定义错误日志? [英] How to create an error log or custom error log within an SSIS Package?

问题描述

我正在研究 SQL 2008 - SSIS 包.我正在从 C# 代码执行包.这是简单的平面文件 (.csv) 到 SQL 表.

I am working on SQL 2008 - SSIS Package. I am executing the package from C# code. It's simple Flat File ( .csv) to SQL Table.

从 C# 代码中，我得到的执行结果为 SUCCESS，但在包中 Transactions 没有被采用，所以实际上没有导出值.

From the C# code, I get execution result as SUCCESS but inside the package Transactions have not taken, so values are not actually exported.

我需要通过阅读日志文件来调试它.

I need to debug it, by reading the log file.

如何在包内创建错误日志或日志文件?

How to create Error Log or Log File inside the Package?

推荐答案

这是在 SSIS 中配置日志记录功能的一种方法.在此示例中，我将发送邮件任务配置为失败，并启用了基于 SQL Server 的日志记录，该日志记录将在包失败时存储 OnError 和 OnTaskFailed 消息.还有其他一些您可能感兴趣的活动.在我工作过的项目中，这两项任务帮助我确定了 SSIS 包中出现的大部分问题.

Here is one way of configuring logging feature in SSIS. In this example, I have Send Mail Task configured to fail and I have enabled the SQL Server based logging which will store the OnError and OnTaskFailed messages when the package fails. There are other events that could be of interest to you as well. In the projects that I have worked, these two tasks have helped me to identify most of the issues that occur in SSIS packages.

分步过程:

1. 本示例中的 SSIS 包包含一个发送邮件任务，配置如屏幕截图 #1 所示.它被配置为失败，因此我们可以在日志表中看到一些错误消息.
2. 点击 SSIS 包.
3. 在菜单上，选择 SSIS --> 日志记录...请参阅屏幕截图 #2.
4. 在配置 SSIS 日志:"对话框中，选择提供程序类型并单击添加".我为这个例子选择了 SQL Server.选中Name 复选框并在Configuration 列下提供数据源.这里SQLServer 是连接管理器的名称.SSIS 将在您选择的数据库中创建一个名为 dbo.sysssislog 的表和存储过程 dbo.sp_ssis_addlogentry.请参阅下面的屏幕截图 #3.
5. 如果您需要捕获错误，请选中复选框 OnError 和 OnTaskFailed.请参阅下面的屏幕截图 #4.
6. 数据流任务中的示例包执行如下面的屏幕截图 #5 所示.
7. 日志表 dbo.sysssislog 的示例输出显示在下面的屏幕截图 #6 中.我只显示了几列 id、event、source 和 message.表中还有其他列.消息列包含错误消息，在这种情况下，发送邮件任务中提到的服务器名称是错误的.源列包含失败的任务.在本例中，包名称为 SSISLoggingExample，发送邮件任务命名为 电子邮件任务.错误消息将从任务到包级别冒泡.因此，错误消息会在任务和包级别记录两次.

1. SSIS package in this example contains a Send Mail Task configured as shown in screenshot #1. It is configured to fail so we can see some error messages in the log table.
2. Click on the SSIS package.
3. On the menus, select SSIS --> Logging... Refer screenshot #2.
4. On the Configure SSIS Logs: dialog, select the provider type and click Add. I have chosen SQL Server for this example. Check the Name checkbox and provide the data source under Configuration column. Here SQLServer is the name of the connection manager. SSIS will create a table named dbo.sysssislog and stored procedure dbo.sp_ssis_addlogentry in the database that you selected. Refer screenshot #3 below.
5. If you need to capture the errors, select the checkbox OnError and OnTaskFailed. Refer screenshot #4 below.
6. Sample package execution within data flow task is shown in screenshot #5 below.
7. Sample output of the log table dbo.sysssislog is shown in screenshot #6 below. I have only displayed few columns id, event, source and message. There are other columns in the table. Message column contains the error message, here in this case the server name mentioned in the Send Mail Task is wrong. Source column contains the task where it failed. Here in this case, the package name is SSISLoggingExample and Send Mail Task is named as Email Task. Error messages will bubble up from task to the package level. Hence, the error message is logged twice under the task as well as at the package level.

希望有所帮助.

屏幕截图 #1:

屏幕截图 #2:

屏幕截图 #3:

屏幕截图 #4:

屏幕截图 #5:

屏幕截图 #6:

这篇关于如何在 SSIS 包中创建错误日志或自定义错误日志?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！