Take the 2-minute tour ×

Coming from a different ETL tool, I'm trying to figure out how to get (production) statistics on each component as it runs in SSIS.

For example, if the flat file is reading from an external source that has a high deviation (the rows/sec changes drastically at different times), I would like to know that information.

If an SSIS has a significant 'slow point' (buffer filling up / data stream impacted), I would also like to know that information.

And using sprocs for example from the DMV's, the CPU time and readIO/writeIO would also be ideal (and useful for people showing improvement by moving from sproc to SSIS in a consistent/measurable approach).

The reason I'm asking this question is I see the rows going through BIDS during debugging, but it may not reflect the actual rows/sec on each component in production.

How would one either enable/introspect/obtain these kinds of statistics for production environments (even if it takes a small hit, the numbers are a big deal).



share|improve this question
Off the top of my head, onpre/onpost execute events will records when an executable begins and ends. Inside a dataflow, you can log the OnPipelineRowsSent event to see the actual timings within a dataflow component. I'll have to think about whether the ability to see metrics on the source component is something exposed to us. I'll repost as an answer if I have any luck –  billinkc Apr 16 '12 at 15:43
Thanks for the OnPipelineRowsSent, I came across that a couple of times but kept trying to use the EventHandlers which, I do not remember the post, but point-blank said you can not get access to the PipelineRowsSent (or any row count metric) from the EventHandlers, so you are left with the Log Providers (for when rowcount metric is needed). This is a very good post on the Log Provider approach towards rows/sec (rows per minute, rpm, in their terminology): weblogs.sqlteam.com/jamesn/archive/2008/02/11/60502.aspx Still no rowcnt/sec for flat file source input stream, important. –  dhartford Apr 16 '12 at 19:37
add comment

2 Answers

up vote 1 down vote accepted

This is difficult to do in SSIS 2005. I have seen the runtime engine "just stop" when trying to perform task-level logging from event handlers in complex SSIS packages. One thought: to instrument the Data Flows only by adding Row Count Transformations just after Source Adapters and on each Data Flow Path that outputs rows. Then add an Execute SQL Task to each Data Flow Task's OnPreExecute event handler to log the start of execution, and add another Execute SQL Task to the corresponding OnPostExecute event handler. In the onPostExecute logic, store the row counts and the end time of the data flow task execution. I believe that will provide enough metrics to calculate throughput for the data flow pipeline.

Hope this helps, Andy

share|improve this answer
add comment

Not sure if it will help, but maybe you can try to configure logging on your package and select "SSIS log provider for SQL Server Profiler"

It shows several information between begin and end of the DataSource Processing

share|improve this answer
add comment

Your Answer


