SSIS 2012, Part 03 of 11: Data Flows and Tasks
Interactive

SSIS 2012, Part 03 of 11: Data Flows and Tasks

LearnNow Online
Updated Aug 22, 2018

Course description

The data flow task is a special control flow task that moves data from a data source to a data destination, optionally transforming the data in various ways as it moves. It is so important and complex that unlike any other control flow tasks, the data flow task has its own designer in SQL Server Data Tools. This is where you’re likely to spend most of your time when developing any non-trivial Integration Services package that moves data rather than just performs other control flow tasks. As a data flow moves data from a source to a destination, you can perform various data flow transformations on that data. These transformations tasks let you look up data from an external source change the contents of a field to upper case sort the data, merge the data flow with other flows, and much more. The data flow task is the single most important task in a control flow and performs the majority of the work in an ETL, Extract, Transform and Load, an ETL process. In this course you’ll learn about the various components that you can use to build the data flow. Including the various data sources and destinations that you can use to read data and store it in its final resting place. Then you’ll learn about the transformations that you can use to modify the data and its scheme as it moves through the data flow pipeline. You can change a fields data type, modify its contents, split the data flow in to multiple pipelines, merge data flows, perform various kinds of look up, and much more. Data flows are key part of Integration Services and its well worth the time getting to know them well.

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

86 Minutes

Time to complete

321 Minutes

Course Outline

Data Flows and Components

The Data Flow Task and Data Sources (25:06)

  • Introduction (00:36)
  • The Data Flow Task (01:32)
  • Data Flow Components (02:23)
  • Data Flow Pipeline (02:28)
  • Data Sources and Destinations (01:11)
  • Data Flow Sources (03:48)
  • Demo: Data Flow Task and Sources (03:28)
  • Demo: Create Data Flow Task (01:37)
  • Demo: Create OLE DB Data Source (00:42)
  • Demo: OLE DB Source Editor (03:22)
  • Demo: OLE DB Column Editor (03:34)
  • Summary (00:19)

Data Flow Destinations (19:54)

  • Introduction (00:29)
  • Data Flow Destinations (00:52)
  • Data Flow Destinations pt. 2 (04:00)
  • Demo: Data Flow Destination (06:29)
  • Demo: Provide Destination for Data (01:32)
  • Demo: Data Source Reader Editor (03:08)
  • Demo: Run and Test Data Source (01:02)
  • Source and Destination Assistants (01:39)
  • Summary (00:39)

Data Flow Transformations (09:55)

  • Introduction (00:53)
  • Row Transformations (01:44)
  • Rowset Transformations (02:07)
  • Split and Join Transformations (01:48)
  • Business Intelligence Transformations (01:17)
  • Other Transformations (01:08)
  • Summary (00:54)

Using Transformations (24:35)

  • Introduction (00:26)
  • Demo: Add New SSIS Package (00:33)
  • Demo: Add Data Flow Demo (00:56)
  • Demo: Create Connection (05:41)
  • Demo: Create Second Data File (01:51)
  • Demo: Combine Data Flows (03:47)
  • Demo: Sort Data in Pipeline (02:42)
  • Demo: Add Audit Transformation (01:50)
  • Demo: Add Excel Destination (04:09)
  • Demo: Execute Package (01:32)
  • Summary (01:01)

Modifying Data Flows (06:52)

  • Introduction (01:12)
  • Demo: Modifying Data Flows (01:03)
  • Demo: Inserting Transformation (00:46)
  • Demo: Error List (00:41)
  • Demo: Mapping Destinations (00:39)
  • Demo: Resolve References (01:52)
  • Summary (00:36)
;