Microsoft® SQL Server® 2008 Integration Services: Problem-Design-Solution

12

SSIS Package Scaling

Have you ever asked the question, “Why is my SSIS package running so long?” Or, have you ever thought, “Did I design this in the best way possible?”

In this last chapter, you learn how to identify SSIS performance issues and understand how to resolve issues through proper design and tuning. This topic is not new, as ways to achieve scalability have been presented throughout the book (such as how to efficiently work with transactions and snapshots, and how to scale your dimension processing). But the difference is that this chapter hones the discussion of scalability to several key areas, including identifying bottlenecks, using SQL in the right places, dataflow tuning, and destination adapter optimization.

By now, you have the main approach to the Problem-Design-Solution series down pat. This chapter fits the same approach, with a slightly different focus in the “Problem” section. Because this chapter discusses scalability, the first section, “Problem,” is about identifying the performance problems in SSIS. Here’s what you can expect in this chapter:

• “Problem” — The “Problem” section answers the question, “Where’s my bottleneck?” It dives into identifying long-running tasks, slow destinations, intensive transformations, and sluggish sources.
• “Design” — The “Design” section presents the best-practice design patterns for ETL problems, which include the balanced use of database features and the SSIS data flow.
• “Solution” — The “Solution” section delves into ...