Now, let's go for the real stuff! This chapter will cover many topics that will lay out the foundations of a simple and effective ETL solution. Over the years, I have seen many SSIS implementations and one of the goals of this chapter is to give the readers the following:
- A simple but effective SSIS framework
- SSIS development best practices
- New data source integrations
All remaining chapters assume that we want to load a data warehouse that is a star schema with its staging area.
The source (operational) database used is AdventureWorksLT, an old well-known database. The following diagram describes the source database that we're going to use:
From this database, we'll insert data in a staging area and finally into a data warehouse. The staging area and the data warehouse will be separated in schemas in a database that we'll manage using SSDT.
The following diagram is the representation that describes the staging schema of the AdventureWorksLTDW database:
Not all tables are copied...
