Chapter 46. Optimizing SSIS for dimensional data loads
SQL Server Integration Services (SSIS) is Microsoft’s enterprise solution for extract, transform, and load (ETL) operations from a wide variety of sources to any number of destinations. In this chapter, I’ll talk about practical methods for optimizing SSIS ETL data flows. The examples I’ll use are dimensional data loads, because they provide some of the most interesting data loading scenarios, but the optimization opportunities I’ll discuss here are useful in many different SSIS ETL scenarios.
Before we dive into performance tuning, I’ll define the term dimensional data for those readers who haven’t built ETL for dimensional data marts yet and may not be familiar with the terminology. Dimensional data marts are stored in relational database management systems (RDMBSs), like SQL Server, using either a star schema or snowflake schema with a fact table and its related dimension tables.
