SSIS 2014, Part 05 of 11: Join Transformations
Interactive

SSIS 2014, Part 05 of 11: Join Transformations

LearnNow Online
Updated Aug 22, 2018

Course description

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 transactional source data base 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 in to 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.


Prerequisites

This course assumes that you have a basic familiarity with the concept of relational databases and a basic understanding of what SQL Server is and the high-level tools in it, as well as how to create and manage objects using Management Studio. You should also have a basic understanding of how SQL Server implements security, including its authentication and authorization schemes, and how to assign permissions on securable objects to principals. You should know the fundamentals of Transact-SQL to write queries to retrieve data and join data from multiple tables, and how to execute scripts using the query editor in Management Studio. You must also know how to connect to an instance of SQL Server 2012 using the various connection dialog boxes in Management Studio and development tools. It will be very helpful, but not absolutely necessary, to have experience with .NET development using Visual Studio 2012 or later for the portions of the course that deal with SQL Server Data Tools (which is a lot of it). At the very least, we’ll assume that you are well familiar with the Visual Studio user interface. This course assumes no prior knowledge of SQL Server Integration Services.


Meet the expert

Don Kiely

Don Kiely is a featured instructor on many of our SQL Server and Visual Studio courses. He is a nationally recognized author, instructor, and consultant specializing in Microsoft technologies. Don has many years of teaching experience, is the author or co-author of several programming books, and has spoken at many industry conferences and user groups. In addition, Don is a consultant for a variety of companies that develop distributed applications for public and private organizations.

Video Runtime

87 Minutes

Time to complete

324 Minutes

Course Outline

Join Transformations

Merge Join Transformation (32:51)

  • Introduction (00:32)
  • The Merge Join Transformation (04:07)
  • Demo: IS Package (04:20)
  • Demo: Database Connection (04:41)
  • Demo: Data Sources (02:40)
  • Demo: Merge Join (04:30)
  • Demo: Sort Transformation (04:44)
  • Demo: Input Columns (03:23)
  • Demo: Union All (03:25)
  • Summary (00:26)

Merge Join Transformation Cont. (16:00)

  • Introduction (00:30)
  • Demo: Missorted Data (02:36)
  • Demo: Category Data Source (04:33)
  • Demo: Merge Join (02:55)
  • Demo: More Data Flows (04:50)
  • Summary (00:34)

The Lookup Transformation (22:16)

  • Introduction (00:55)
  • The Lookup Transformation (01:59)
  • Demo: Package Setup (03:21)
  • Demo: Lookup Transformation (03:40)
  • Demo: Lookup Columns (03:12)
  • Demo: Union All (03:11)
  • Demo: More Lookups (05:18)
  • Summary (00:39)

Lookup Transformation Caching (16:15)

  • Introduction (00:24)
  • Caching (05:04)
  • The Cache Connection Manager (02:21)
  • Cache Connection Manager (Cont) (01:54)
  • Demo: Cache Modes (06:11)
  • Summary (00:18)
;