How to measure duration of different tasks in a data flow task?

Question

I am working on optimizing a data flow task, I thought I will see which component takes how much time. I got these tasks:

How should I find out how much time each took? Except time if i can find out about other resources like memory, CPU etc. that will be great.

Primary I am looking at time each component took.

Answer 1

For quick review of the elapsed time see the Execution Results Tab. It will display the Elapsed Time when a task/component starts and finished.

Also

Monitoring Performance of the Data Flow Engine This is a little more involved and it slows down my PC when doing it. YMMV.

Also SSIS contains a pretty good logging mechanism for errors, information and a myriad of other events if you need to troubleshoot something:

From your menu SELECT SSIS, Logging...

You'll need to specify a provider. To keep it simple until you get the hang of it I'd suggest SSIS log provider for Text Files. It's CSV format and you can customize the information returned.

To monitor all of the components in the package: Click the Details tab and select OnInformation and OnPipelineRowsSent as suggested by billinkc. Be mindful that Logging is useful for troubleshooting, but OnPipelineRowsSent is verbose so you might want to remove that event from the log when you deploy to production to reduce write times and log sizes.

To monitor only a specific task: Uncheck the package and then check the data flow task/component from the package tree view that you would want to monitor. After you select it then choose the events you want logged start with OnInformation (and OnPipelineRowsSent as suggested by billinkc).

Answer 2

If I want to determine timings between components on a data flow, I use the native logging and capture the OnPipelineRowsSent event. This indicates how many rows have moved between transformations. James has a good query and writeup on it over on sqlteam

One thing to note, you cannot log OnPipelineRowsSent event until a data flow exists in a package. I always felt that was a shame from the standpoint of creating template packages.