如何在完成执行后检查 SSIS 包作业结果? [英] How to check the SSIS package job results after it has completed its execution?

问题描述

我有一个 SSIS 包，可将数据导入 SQL Server 2008 数据库.我已经在 SQL Server 代理中设置了计划作业来运行该包.当我查看历史记录时，我只能看到作业是否成功运行.除此之外，我看不到其他消息.

I have an SSIS package which imports the data into the SQL Server 2008 database. I have set up the schedule job in the SQL Server Agent to run that package. When I check the history, I could only see whether the job ran successfully or not. I could not see other messages apart from that.

我想知道每次执行作业时导入了多少条记录.我该如何监控?我应该使用 SSIS 包中的附加组件还是在 SQL Server 代理作业设置中设置一些配置?

I would like to know how many records are imported whenever the job is executed. How can I monitor that? Should I use the additional components in SSIS package or set some configurations in SQL Server Agent Job Setup?

我在 SQL Server 代理作业设置中找到了一些日志记录工具，但我不确定它是否能满足我的要求.

I found some logging facilities in SQL Server Agent Job Setup but I am not sure it can fulfill my requirements or not.

推荐答案

如果您只对了解正在处理的列感兴趣而对进一步使用的信息不感兴趣，一种可能的选择是使用 SSIS 日志记录功能.以下是它如何处理数据流任务.

If you are just interested in knowing the columns being processed and not interested with the info for further use, one possible option is making use of the SSIS logging feature. Here is how it works for data flow tasks.

1. 点击 SSIS 包.
2. 在菜单上，选择 SSIS --> 日志记录...
3. 在配置 SSIS 日志: 对话框中，选择提供程序类型并单击添加.我为这个例子选择了 SQL Server.选中名称复选框并在配置列下提供数据源.这里 SQLServer 是连接管理器的名称.SSIS 将在您选择的数据库中创建一个名为 dbo.sysssislog 的表和存储过程 dbo.sp_ssis_addlogentry.请参阅下面的屏幕截图 #1.
4. 如果您需要处理行，请选中 OnInformation 复选框.在示例中，包执行成功，因此在 OnInformation 下找到了日志记录.您可能需要根据您的要求微调此事件选择.请参阅下面的屏幕截图 #2.
5. 这是数据流任务中的示例包执行.请参阅下面的屏幕截图 #3.
6. 这是日志表 dbo.sysssislog 的示例输出.我只显示了 id 和 message 列.表中还有许多其他列.在查询中，我仅过滤名为Package1"的包和事件OnInformation"的输出.您可以注意到 ID 为 7、14 和 15 的记录包含已处理的行.请参阅下面的屏幕截图 #4.

1. Click on the SSIS package.
2. On the menus, select SSIS --> Logging...
3. 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 #1 below.
4. If you need the rows processed, select the checkbox OnInformation. Here in the example, the package executed successfully so the log records were found under OnInformation. You may need to fine tune this event selection according to your requirements. Refer screenshot #2 below.
5. Here is a sample package execution within data flow task. Refer screenshot #3 below.
6. Here is a sample output of the log table dbo.sysssislog. I have only displayed the columns id and message. There are many other columns in the table. In the query, I am filtering the output only for the package named 'Package1' and the event 'OnInformation'. You can notice that records with ids 7, 14 and 15 contain the rows processed. Refer screenshot #4 below.

希望有所帮助.

屏幕截图 #1:

屏幕截图 #2:

屏幕截图 #3:

屏幕截图 #4:

这篇关于如何在完成执行后检查 SSIS 包作业结果?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！