SSIS data flow execution monitoring and logging

Has anyone come up/determined a generic way to capture and log indicative information within a data flow in SSIS - e.g., a number of rows selected from the source, transformed, rejected, loaded, various timestamps around these events, etc.? I am trying to avoid having to build a custom solution for each of the packages that I will have (of which there will be dozens). Ideally, I'd like to have some sort of a generic component (such as a custom transformation) that will hide the implementation details and provide a generic interface to the package.

It is not too difficult to achieve something similar on the control flow level, but once you get into data flows things get complicated.

Any ideas will be greatly appreciated.

[751 byte] By [chianuri] at [2007-12-22]
# 1
HI, I did you have a look at Reporting services reports for Integration Services? Particularely the OnPipelineRowSent one? If you get the views that are built for its sources, you should get what you need from sysdtslog90 table.

If you download reports for SSIS at http://www.microsoft.com/downloads/details.aspx?familyid=d81722ce-408c-4fb6-a429-2a7ecd62f674&displaylang=en, you will find a file into the reports compressed file that is called SQL Objects.sql. Inthere you will find views that will parse the log table.

HTH,

Ccote

ccote at 2007-8-30 > top of Msdn Tech,SQL Server,SQL Server Integration Services...
# 2

Thanks, Ccote. This is really helpfull.

I also found an excellent article on building a custom logging provider in Jamie Thomson's blog:

http://blogs.conchango.com/jamiethomson/archive/2005/06/11/1593.aspx

chianuri at 2007-8-30 > top of Msdn Tech,SQL Server,SQL Server Integration Services...
# 3

I took the basis of my implementation there. This blogs has lots of useful information's. Basically, I call packages from the pre-execute, error and post execute events that log infornations in metadata tables on what I have to load, what have been loaded and the error message if an error occurs.

Ccote

ccote at 2007-8-30 > top of Msdn Tech,SQL Server,SQL Server Integration Services...

SQL Server

Site Classified