SSIS 2014, Part 05 of 11: Join Transformations

Trivial Integration Services packages can take data straight from a data source and dump it unchanged into a destination. That’s certainly a valued use of a package but it doesn’t take advantage of all that you can do to data as it moves from place to place. More often you’re likely to join data for multiple sources together into a single data flow. For example, in an online transaction source database you might have normalized product information spread across several tables, including product, product category, product description and others. That can be an efficient structure for day to day online operations but if you need to insert the data into a database warehouse for analyses, normalized data is very inefficient. So a package you create to populate the data warehouse would need to join several sources together into a single data flow and that’s where the join transformations are useful. In this course you’ll learn about two of the join transformations built into data flows. The Merge join transformation merges two data flows into a single output flow, performing a join operation on the data. This is the same operation you can perform in T-SQL using the inner-join or outer-join syntax in a select statement. The major difference is that the transformation operates on a stream of data coming in from the data flow pipeline. Whereas the T-SQL type of join is a set operation. The other join transformation is the Lookup transformation. This performs an inner join on its single input data flow using a data set from a source defined within the transformation instead of using another data flow. What really sets this component off is its ability to cash look up data in very flexible and powerful ways. The join transformations provide powerful features to process data in an Integration Services package and this course will get you started understanding on how to put them to use.

Each LearnNowOnline training course is made up of Modules (typically an hour in length). Within each module there are Topics (typically 15-30 minutes each) and Subtopics (typically 2-5 minutes each). There is a Post Exam for each Module that must be passed with a score of 70% or higher to successfully and fully complete the course.

SSIS 2014, Part 05 of 11: Join Transformations