How to measure SSIS component/transformation performance

To assist in improving performance of your SSIS packages, you are essentially looking at performing a process of elimination performance analysis. You can remove transformations isolating just the source and destination to see if the transforms are causing the issue, remove the destinations and replace them with a terminator such as the derived column or a row count. You can also use the trash destination from Konesans for a purely development approach (https://www.konesans.com/trashdest.aspx).

 

Elizabeth Vitt’s whitepaper(https://www.microsoft.com/technet/prodtechnol/sql/2005/ssisperf.mspx), contains very good information on overall SSIS performance and is a great reference. This whitepaper also discusses execution trees and buffer usage, but for measuring performance and looking for bottlenecks you are essentially going through a process of elimination.

 

For another approach at performance tuning, BIDSHelper (https://www.codeplex.com/bidshelper) has some eye candy, new functionality for BIDS, and also gives very useful measurement tools Gantt Charts, Statistics Grid and Statistics Trend reports.

 

Gantt Chart

Statistics Trend