How to track status of rows successfully processed

问题:

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?

回答1:

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. 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 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

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.

Hope that helps.

Screenshot #1:

Screenshot #2:

Screenshot #3:

Screenshot #4:

回答2:

You can multicast the flat file, or use a trigger on the table you're inserted data to. If it's a table that's being audited you'll probably want to know whenever any data is inserted.