Chapter 7

Joining Data

WHAT’S IN THIS CHAPTER?

• The Lookup Transformation
• Using the Merge Join Transformation
• Building a basic package
• Using the Lookup Transformation
• Loading Lookup cache with the Cache Connection Manager and Cache Transform

In the simplest ETL scenarios, you use an SSIS Data Flow to extract data from a single source table and populate the corresponding destination table. In practice, though, you usually won’t see such trivial scenarios: the more common ETL scenarios will require you to access two or more data sources simultaneously and merge their results together into a single destination structure. For instance, you may have a normalized source system that uses three or more tables to represent the product catalog, whereas the destination represents the same information using a single denormalized table (perhaps as part of a data warehouse schema). In this case you would need to join the multiple source tables together in order to present a unified structur....