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

1

SSIS Solution Architecture

Imagine that this is the first day of a new internal or client project. You will have responsibility on the data and processing layer of the solution, which involves processing data — a lot of data — from several sources, and then either integrating systems, or maybe consolidating data for reporting. Maybe your background is a developer, or a database administrator (DBA), or a data designer, and you know SSIS fairly well. But now they are calling you the “data and ETL architect.”

ETL is the acronym for Extraction, Transformation, and Loading, which is used to describe the data-processing layer in a data-integration or data warehouse solution.

The project manager approaches you and says that the Vice President of Technology has asked the team to give him an estimate of the infrastructure needed. Furthermore, the business owner wants a high-level overview of how the solution architecture will help the company achieve the business need most efficiently. The project manager also wants your thoughts on the best way to approach the solution, how the development should be coordinated between team members, and how deployment should be handled.

Three meetings are scheduled for tomorrow to address these things, and you’ve been asked to provide your feedback in those areas.

Where do you start? How should you approach the solution design with SSIS as the main technology? How should all the pieces work together?

This chapter examines how to lay the foundation for ...