3

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?

Mike G
  • 4,232
  • 9
  • 40
  • 66
largo68
  • 609
  • 2
  • 16
  • 29
  • I would like to populate an audit table with the number of rows inserted into the target table . Something similar to the @@rowcount?? – largo68 Jul 19 '11 at 12:28

2 Answers2

11

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:

Logging

Screenshot #2:

Events

Screenshot #3:

Execution

Screenshot #4:

Data

  • by the way, I followed your explanation in my package but I cannot find the table 'dbo.sysssislog' in SQL Server. Is it under System Databases? – largo68 Jul 19 '11 at 12:59
  • strange I don't have it in my System Tables – largo68 Jul 19 '11 at 13:15
  • Maybe it is because my server is sql server 2000? – largo68 Jul 19 '11 at 13:24
  • OK never mind! I think this is where issue comes from because your SELECT * FROM sys.tables WHERE name LIKE '%ssis%' OR name LIKE '%dts%' doesn't return anything.. – largo68 Jul 19 '11 at 13:29
  • Otherwise, is it not possible to use the row count transformation to have something similar? – largo68 Jul 19 '11 at 13:31
0

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.

Yuck
  • 49,664
  • 13
  • 105
  • 135