Datasheet

Chapter 1: SSIS Solution Architecture
2
This chapter examines how to lay the foundation for successful solutions based on SQL Server
Integration Services (SSIS). And, in fact, this whole book is about SSIS solutions to real - world
requirements and challenges. It addresses questions such as the following:
What are the problems and issues?
What are the design principles?
How do you put it all together for a complete and successful solution?
Before you dive into the technical challenges of a project, you must first step back and ensure that you
are laying the right foundation. Jumping right in is tempting! But resist the urge, because you want to
(and need to) set the precedence and patterns for the solution upfront. If you don ’ t, chances are you
won ’ t be able to go back and make changes down the road.
As with all chapters in this book, this chapter is organized into the following three major sections:
“ Problem ” — Coordinating and architecting an SSIS solution is not an easy task. The “ Problem ”
section reveals some common challenges and common mistakes when planning and extending
your ETL environment.
“ Design ” — The “ Design ” section in this chapter examines the right approach to a project, and
the long - term project aspects that you should set in motion early in a project.
“ Solution ” — In many ways, the remainder of this book provides you with the solutions to make
it all work together. This section launches you into the rest of the book, and shows how you can
follow the chapters to build or redesign your SSIS solution.
Problem
Data and ETL projects have many challenges. Some challenges relate to data, some to enterprise
integration, some to project coordination, and some to general expectations. This section begins by
looking at the bigger picture of data within an organization, but then quickly looks at ETL projects and
SSIS packages and execution.
Macro Challenge: Enterprise Data Spaghetti
Maybe your SSIS project is only a small solution in a bigger enterprise pond. The problem is that it can
still cause a ripple effect when you tie it into your environment. Or, you can have challenges caused by
an unwieldy enterprise environment when you try to implement your solution.
Figure 1 - 1 shows a not - so - nice telephone/electricity pole that illustrates the data nervous system of
many organizations.
❑
❑
❑
❑
❑
❑
c01.indd 2c01.indd 2 9/24/09 11:26:27 AM9/24/09 11:26:27 AM