如何跟踪 SSIS 数据流任务中成功处理或失败的行的状态? [英] How to track status of rows successfully processed or failed in SSIS data flow task?

查看:33
本文介绍了如何跟踪 SSIS 数据流任务中成功处理或失败的行的状态?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有一个非常简单的数据流任务,从 FF 读取数据并将数据插入表中.同时我想在一个Audit表中写入,插入了多少行,创建日期...

I have a very simple Data flow task reading data from a FF and inserting the data in a table. At the same time I would like to write in an Audit table, how many rows have been inserted, the created date...

我怎样才能轻松做到这一点?

How can I do that easily?

推荐答案

如果您只对成功处理的行数或遇到错误的行数感兴趣,那么您可以使用内置的 SSIS 日志记录 功能.请检查以下提到的步骤.我希望这个例子能给你一个想法.我只显示了日志表中的两列,但还有其他有用的字段,如开始时间、结束时间等,该示例是在 SSIS 2008 R2

If you are interested only in the number of rows being successfully processed or number of rows that encountered errors, then you can make use of in-built SSIS logging feature. Please check the below mentioned steps. I hope the example gives you an idea. I have displayed only two columns from the log table but there are other useful fields like starttime, endtime etc., The example was created in SSIS 2008 R2

  1. 点击SSIS包.

在菜单上,选择 SSIS -->记录...

在配置 SSIS 日志: 对话框中,选择提供程序类型并单击添加.我为这个例子选择了 SQL Server.选中名称复选框并在配置列下提供数据源.这里 SQLServer 是连接管理器的名称.SSIS 2008SSIS 2008 R2 将创建名为 dbo.sysssislog 的表和存储过程 dbo.sp_ssis_addlogentry您选择的数据库.请参阅下面的屏幕截图 #1.SSIS 2005中的表名为dbo.sysdtslog90,存储过程名为dbo.sp_dts_addlogentry

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 2008 or SSIS 2008 R2 will create a table named dbo.sysssislog and stored procedure dbo.sp_ssis_addlogentry in the database that you selected. Refer screenshot #1 below. The table name in SSIS 2005 is dbo.sysdtslog90 and the stored procedure is named as dbo.sp_dts_addlogentry

如果您需要处理行,请选中 OnInformation 复选框.在示例中,包执行成功,因此在 OnInformation 下找到了日志记录.您可能需要根据您的要求微调此事件选择.请参阅下面的屏幕截图 #2.

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.

这是数据流任务中的示例包执行.请参阅下面的屏幕截图 #3.

Here is a sample package execution within data flow task. Refer screenshot #3 below.

这是日志表 dbo.sysssislog 的示例输出.我只显示了 idmessage 列.表中还有许多其他列.在查询中,我仅过滤名为Package1"的包和事件OnInformation"的输出.您可以注意到 ID 为 7、14 和 15 的记录包含已处理的行.请参阅下面的屏幕截图 #4.

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屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆