Learn your way! Get started

SSIS 2014, Part 05 of 11: Join Transformations

with expert Don Kiely


Watch trailer


Course at a glance

Included in these subscriptions:

  • Dev & IT Pro Video
  • Dev & IT Pro Power Pack

Release date 11/3/2014
Level Intermediate
Runtime 1h 27m
Closed captioning Included
Transcript Included
eBooks / courseware Included
Hands-on labs Included
Sample code Included
Exams Included


Enterprise Solutions

Need reporting, custom learning tracks, or SCORM? Learn More



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.

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.

Learning Paths

This course will help you prepare for the following certifications and exams:
MCSE: Business Intelligence
MCSA: SQL Server 2012/2014
MCSE: Data Platform
70-467: Designing Business Intelligence Solutions with Microsoft SQL Server
70-463: Implementing a Data Warehouse with Microsoft SQL Server 2012/2014

This course is part of the following LearnNowOnline SuccessPaths™:
SQL Server Integration Services

Meet the expert

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.

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)