如何跟踪SSIS数据流任务中成功处理或失败的行的状态? [英] How to track status of rows successfully processed or failed in SSIS data flow task?
问题描述
我有一个非常简单的数据流任务，该任务从FF读取数据并将数据插入表中.同时，我想在审计"表中写入已插入多少行，创建的日期...
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 logging特征.请检查以下提到的步骤.我希望这个例子能给您一个想法.我只显示了日志表中的两列，但还有其他有用的字段，例如开始时间，结束时间等.该示例是在 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
-
单击
SSIS package.
在菜单上，选择SSIS --> Logging...
在配置 SSIS日志:"对话框中，选择提供程序类型，然后单击添加".在此示例中，我选择了SQL Server.选中名称复选框，并在配置列下提供数据源.在此，SQLServer是连接管理器的名称. SSIS 2008或SSIS 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的示例输出.我只显示了 id 和 message 列.表格中还有许多其他列.在查询中，我仅过滤名为" 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屋！
