Datasheet

Chapter 1: SSIS Solution Architecture
4
Unknown and uncontrollable data processing — The operations that process data are not
centralized, and, in many cases, unknown because of department applications that are created
without coordination with other applications. Processes run at uncontrolled times, and may
impact systems within the processes even during peak times, which affects work efficiency.
Fragile enterprise changes — Changes to applications are difficult and costly. They may break
processes, or cause data integration or reporting applications to be inaccurate.
Delayed data access — Even when the processes are somewhat controlled, the complicated system
dependencies cause delays in data availability and nightly overhead processes that often run
into mid - morning schedules. When they break, customer perception and employee efficiency
are affected.
The “ Design ” section later in this chapter discusses how to approach your SSIS - based ETL project in the
right way, and ensure that you are helping to solve the problem, rather than adding to it.
Micro Challenge: Data-Processing Confusion
Another common problem with data processing is when the logic contained to process data is overly
complicated and confusing. Just like the macro enterprise problem, this problem usually is the result of
changes over time where logic is modified and appended. It usually comes in one of two ways:
Runaway stored procedures — Procedures that run with complicated logic and lots of temporary
tables, inserts, updates, and/or deletes can be difficult to manage, and are often inefficient.
Supporting the procedures is also very difficult because the logic is difficult to follow, and, many
times, the developers or DBAs who wrote the code are unavailable. Overall, this type of process
requires a lot of administration and wasted time spent on following and learning the process.
Unmanageable packages — SSIS packages can also be designed with difficult - to - follow logic and
sometimes complex precedence with hundreds of components used in a single package. These
kinds of packages have challenges similar to those of runaway stored procedures, such as
troubleshooting and the learning curve required for the process. Figure 1 - 2 shows the control
flow of a package that has too many components to effectively manage. (The SSIS designer is
zoomed in at 50 percent to fit on the screen.)
❑
❑
❑
❑
❑
c01.indd 4c01.indd 4 9/24/09 11:26:29 AM9/24/09 11:26:29 AM