Most warehousing projects continue to tweak and maintain their
ETL process long after the warehouse is in production; be sure to include ongoing
maintenance in any project and budget projections.
ETL Using SQL Server Integration Services
Microsoft SQL Server 2005 ships with a robust, enterprise-scale ETL tool called
SQL Server Integration Services, or SSIS. SSIS creates what are called packages,
and each package contains some piece of the ETL process. Imagine writing a book;
an author could open up a word processor and type the entire book in a single
document. Instead, most often, the author creates a separate file for each chapter
because it is easier to maintain and edit. Similarly, a single SSIS package could
contain the entire ETL process, but typically it does not. Instead, the developers
creating the ETL process create numerous packages, each providing a few steps of
the entire process. This makes the entire ETL flow easier to maintain and allows for
much better reuse. The flow can be changed as necessary and the different packages
can be run in different orders depending on the needs of the incoming data.
Each package is broken up into to major areas: control flow and data flow. Control
flow is what it sounds like: it controls the overall flow of this particular package. In
the control flow area, there are containers and task which can be added to the flow;
tasks do something specific while containers group together multiple tasks. For
example, there are tasks that send emails (possibly due to completion or an error
condition), run other packages, execute SQL statements, FTP files, and more.
Pages:
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86