There
are containers for looping through the tasks placed in them and containers that ensure
tasks are run in a particular sequence. As one task is completed, it can then flow to
other tasks, and it may flow differently based on whether the task succeeded or failed.
One of the tasks used most often in the Control Flow is the Data Flow Task, which
opens another editor window. Once again, the developer is presented with many
controls. There are controls to connect to both source and destination data sources,
whether those sources are relational databases, XML files, Excel spreadsheets, text
C h a p t e r 3 : D a t a W a r e h o u s i n g a n d B u s i n e s s I n t e l l i g e n c e 45
documents, and so forth. In addition to the data sources and destinations, there are
many controls for data transformations. These include such items as conditional splits,
fuzzy lookups, merging, sorting, handling slowly changing dimensions, and so forth.
A simple example of an SSIS package for a warehouse refresh might be one
in which the first part of the flow executes SQL tasks to empty the star schema
tables. After this step, data flow tasks are called. One task might grab data from a
particular dimension table and load that data. Another data flow might pull data from
multiple sources, convert some columns to a standard format, and place that data in
a dimension table. Another data flow task might handle a slowly changing product
dimension. Finally, a data flow task might load the fact table, looking for any records
with key violations.
Pages:
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87