Summary

When it comes to SSIS scalability, there is no silver bullet. However, there are several ways to isolate performance limitations and identify impacting tasks. Furthermore, since SSIS is a platform, there can be many ways to design the same process. This makes package design important in achieving scalability. Know when and where to use SQL code, and when to employ the data flow.

The critical aspect of SSIS when it comes to scalability is optimizing the data flow, which boils down to memory management and destination optimization. Ensure that your designs are leveraging the features and functionality of SSIS in the right creative ways that limit common bottlenecks such as blocking transformations and large Lookup caches.

Finally, choose a package architecture that considers your source and destination servers and the impact that the data flow will have on those, and, if needed, create a distributed or scaled-out execution architecture. Applying these principles and the others found in this book will take you on the way to designing and implementing stable and scalable SSIS solutions that meet the demanding business requirements for your data processing.