Steps to Improve ETL Performance Using SSIS
Some of the biggest performance tuning wins that I have had over my career have been related to performance tuning queries that are being called from SSIS. So often the actual query performance gets overlooked in the SSIS environment. A few years ago I was able to tune a query that was being called from an SSIS package and reduce the runtime of that package from over an hour, to just 2 minutes. Its not always that big of a win, and there are other performance tuning options besides just tuning the queries, so lets take a look at some of them.
SQL Server Integration Services (SSIS) is one of those components that can help you out with your ETL (Extract, Transform and Load) work, or it can hinder your process if it is a poor performing ETL. There are many things that can easily cause an ETL to run for hours, when it should be running for minutes.
The following tips are intended as things to consider when performance tuning to improve your ETL Performance Using SSIS.