Datasheet

Chapter 1: SSIS Solution Architecture
8
The bottom line is that you will most likely have a disk I/O bottleneck in your data-processing
operations, and you ’ ll need to plan and manage for that to meet your service level agreements (SLAs)
and performance requirements.
Other Challenges
The list of common project challenges can go on and on, but here are a few more:
Data challenges — Of course, you will have data challenges in your project anywhere from
missing records to dirty data to bad data, and you will need to understand those problems as
soon as possible so that you can set the expectations upfront about what can and what cannot be
done about them. Although you can do a lot in SSIS (including fuzzy matching), magic is not on
the component list — you can ’ t make up data that doesn ’ t exist. Don ’ t overpromise. Be realistic.
Corporate political challenges — This book is about solving problems with a technology, namely
SSIS. But, because you are trying to solve problems, you are going to be dealing with people.
Everyone has an agenda, and a lot of times those agendas will not be in your project ’ s best
interest. Watch out for people who are threatened because you are changing the way things are
done (even when it is for the better), or because your solution will be replacing one of their
legacy applications, or because they are territorial about their “ sandbox. ” You want to fix the
enterprise spaghetti shown in Figure 1 - 1, but don ’ t forget that some people have their tribal
knowledge and make their living by keeping the corporate data nervous system tangled.
Requirement and scope challenges — Any project has scope creep. Just be careful about how the
changes affect the project timeline, and don ’ t leave data validation until the last day. You ’ ll get
burned!
Design
Now that you are scared, step back and take a deep breath. Designing an ETL process is doable, and,
with the right approach in mind, you can be successful. This section discusses the overall design
approach to an SSIS - based solution by examining the following:
Choosing the right tool
Solution architecture
Project planning
Package design patterns
Server and storage hardware
Package execution location
❑
❑
❑
❑
❑
❑
❑
❑
❑
c01.indd 8c01.indd 8 9/24/09 11:26:31 AM9/24/09 11:26:31 AM