Chapter 58. SSIS performance tips
Managing performance in SQL Server Integration Services (SSIS) can be an ever-changing battle that depends on many factors, both inside and outside the control of the SSIS developer. In this chapter, we will discuss a few techniques that will enable you to tune the Control Flow and the Data Flow elements of a package to increase performance.
Note
The following information is based on Integration Services in SQL Server 2005, Service Pack 2.
SSIS packages are built around a control flow or a work surface to manage all activity within a package. The control flow is responsible for directing the execution path, as well as for managing predecessors, logging, and variables. The control flow also enables developers to use web services, interact with FTP sites, use custom scripts, and perform many more tasks that can all be linked together in serial or executed in parallel.
Data flow tasks are responsible for extracting, transforming, and loading data from a source to a destination. A source or a destination can be a database table, flat files, memory objects, custom objects, or other supported items. A generous list of components to transform data ships with SSIS and contains such items as row counters, aggregators, lookup transformations, and many more. Data flow tasks reside on the control flow and they can be the only task, or one of many.
