Database Reference
In-Depth Information
Chapter 7
Beginning the ETL Process
with SSIS
I have this hope that there is a better way. Higher-level tools that actually let you see the structure
of the software more clearly will be of tremendous value.
—Guido van Rossum
In the previous chapter, we discussed how to extract data from a source table and transform that data using SQL
programming statements. This is a very traditional approach and works well for many professionals, particularly
SQL programmers, but can feel cumbersome and tedious to work with.
To be successful with the SQL programming approach, you must thoroughly understand the code you are
working with and visualize each action the code performs. The greater the amounts of ETL processing you do
the more difficult it is to visualize and coordinate the programmatic flow of your SQL statements and the more
difficult it is to explain it to others on the team.
In this chapter, we begin creating the ETL process with Microsoft's SQL Server Integration Services (SSIS).
SSIS can help simplify your ETL process by providing the following:
•
A visual overview of the ETL process
•
Visual and programmatic control of a sequence of ETL tasks
•
A way to collectively schedule automations
•
The ability to individually execute tasks and track processing
•
Access to any data source that supports OLE DB or ODBC connections
SSIS is a platform that can perform various tasks pertaining to data collection and automating processes. It
features tools that can be extremely helpful in performing the ETL process. Although the ETL process is its main
function, it has several other features as well, such as the ability to run .NET code, interact with the file system,
and access various operation system resources.
For the purpose of this topic, we focus specically on the common tasks needed for developing BI solutions.
Our goal is to show how easy SSIS is to use in conjunction with the Microsoft SQL Server database engine and to
make the ETL process as pain-free as possible.
