Database Reference
In-Depth Information
Chapter 8
Concluding the ETL Process
with SSIS
Sometimes good things fall apart so better things can fall together.
—Marilyn Monroe
Thus far, the ETL process has taken us through a lot of decision making. Data has had to be cleaned up and
handled with care to ensure it is accurate and that it can be worked with. In Chapter 7, we created an SSIS
package and set up our data connections. We also configured and tested the Execute SQL tasks.
In this chapter, we continue the SSIS process by configuring data flows and data destinations that enable us
to load our data warehouse, and we handle errors and round out this subject by executing the entire package.
Data Flows
Data Flows are the most complex of the SSIS tasks, because they are made up of a composite of many
subcomponents. There are three different types of data flow subcomponents: sources, transformations, and
destinations. Most often sources pull data from tables and views, but you can also extract data from multiple file
types, and even SSAS cubes. Transformations modify, summarize, and clean data. Destinations load data into
tables, cubes, files, or in-memory datasets that can be used by other tasks within the SSIS package itself.
Data flows are configured using the Data Flow tab. You can access this tab at the top of the package designer
window or right-click any data flow task and select Edit from the context menu. Either way, the user interface
navigates to the Data Flow tab (Figure
8-1
).
