Integration Services Fundamentals

By Stacia Misner Varga
This course is for business intelligence developers and database professionals responsible for moving, manipulating, and integrating data and assumes no prior experience with Integration Services
Course info
Rating
(1336)
Level
Beginner
Updated
Jun 6, 2011
Duration
7h 1m
Table of contents
Introduction to Integration Services
Introduction to Integration Services 2m Architecture 3m Package Development 2m Integration Services Projects in BIDS 2m Demo: Creating a Package in BIDS with the Import and Export Wizard 6m Package Designer 2m Introduction to Connection Managers 1m Introduction to Package Execution 1m Demo: Exploring a Package 6m Demo: Exploring a Package 5m Summary 2m
Control Flow Basics
Control Flow Basics 1m Connection Managers 6m 32-bit versus 64-bit Providers 2m Demo: Creating a Connection Manager 3m Tasks 1m Demo: Creating an Execute SQL Task 4m Demo: Creating a Send Mail Task 2m Precedence Constraints 2m Demo: Adding Precedence Constraints 3m Containers 5m Demo: Working with Containers 4m Summary 1m
Control Flow Tasks
Control Flow Tasks 2m Data Preparation Tasks 2m Demo: File System Task 4m Demo: FTP Task 2m Demo: Web Service Task 3m Demo: XML Task 2m Demo: Data Profiling Task 3m Demo: Data Profiling Viewer 4m Process Communication Tasks 3m Demo: Message Queue Task - Send 2m Demo: Message Queue Task - Receive 1m Demo: Execute Package Task 2m Demo: WMI Data Reader Task 3m Demo: WMI Event Watcher Task 3m Demo: Execute Process Task 2m SQL Server Tasks 2m Demo: Bulk Insert Task 3m Analysis Services Tasks 1m Demo: Analysis Services Processing Task 3m Demo: Analysis Services Execute DDL Task 4m Demo: Data Mining Query Task 5m Database Maintenance Tasks 1m Summary 1m
Data Flow Basics
Data Flow Basics 1m Data Flow Task 3m Pipeline Architecture 3m Data Sources 3m Demo: ADO.NET Source 4m Demo: Excel Source 2m Demo: Flat File Source 3m Demo: OLE DB Source 2m Demo: XML Source 2m Demo: Raw File Destination 2m Demo: Raw File Source 1m Data Destinations 3m Demo: OLE DB Destination 3m Demo: DataReader Destination / Run64BitRuntime Property 3m Demo: Excel Destination / Override Connection Manager Data Types 3m Demo: Flat File Destination 2m Demo: SQL Server Destination 3m Analysis Services Destinations 2m Demo: Dimension Processing 1m Demo: Partition Processing 1m Demo: Data Mining Model Training 2m Summary 1m
Data Flow Transformations
Data Flow Transformations 2m Row Transformations 3m Demo: Character Map 5m Demo: Copy Column 2m Demo: Data Conversion 2m Demo: Derived Column 4m Demo: OLE DB Command 4m Rowset Transformations 2m Demo: Aggregate 3m Demo: Sort 3m Demo: Unpivot 3m Demo: Pivot 4m Demo: Percentage Sampling 1m Demo: Row Sampling 1m Split and Join Transformations 3m Demo: Conditional Split 4m Demo: Multicast 1m Demo: Union All 2m Demo: Merge 4m Demo: Merge Join 5m Demo: Lookup 3m Business Intelligence Transformations 2m Other Transformations 2m Demo: Export Column 2m Demo: Import Column 3m Demo: Audit 2m Summary 1m
Expressions
Expressions 1m Expression Builder 1m SSIS Expression Language Syntax: Functions 2m SSIS Expression Language Syntax: Operators and More Variables 2m Variables 3m Demo: Introducing the Variables Window 4m Demo: Working with Variables 6m Expression Usage 2m Demo: Using a Variable in an Expression 6m Demo: Using an Expression with a Connection Manager 4m Demo: Using an Expression to Construct a SQL Statement in the Control Flow 4m Demo: Using Expressions with Precedence Constraints 4m Demo: Using an Expression in the Data Flow 5m Summary 3m
Scripts
Scripts 1m Scripting in Integration Services 2m Script Task Configuration 1m Demo: Configuring a Script Task 2m DTS Object 1m Variables 1m Task Results 1m Demo: Working with the Variables Member 4m Events 1m Demo: Working with the Events Member 1m Script Components 2m Demo: Creating a Data Transformation Script Component 5m Summary 2m
Debugging SSIS Packages
Debugging 2m Breakpoints 2m Debug Windows 1m Demo: Introducing Breakpoints 6m Demo: Working with Breakpoints 3m Script Task Debugging 1m Demo: Debugging a Script Task 3m Script Component Debugging 1m Demo: Debugging a Script Component 2m Data Viewer 3m Demo: Using the Grid Viewer 4m Demo: Using the Histogram Viewer 1m Demo: Using the Scatterplot Viewer 1m Demo: Using the Chart Viewer 1m Summary 2m
Package Reliability
Package Reliability 2m Package Logging 2m Demo: Configuring Logging 5m Demo: Configuring Logging in a Scipt Task and Viewing Logs 4m Error Handling 2m Demo: Adding an Error Output to a Data Flow Task 6m Event Handling 1m Demo: Adding a Task to an Event Handler 2m Transactions 3m Demo: Configuring Transaction Support 4m Checkpoints 3m Demo: Working with Checkpoints 9m Demo: Using Checkpoints with Containerrs 3m Summary 2m
Package Execution
Package Execution 1m Package Execution Utilities 7m Demo: Using DTExecUI 5m Demo: Using DTExec in the command line 1m SQL Server Agent Jobs 1m Demo: Creating a SQL Server Agent Job 2m Package Storage vs. Execution Location 4m Summary 3m
Description
Course info
Rating
(1336)
Level
Beginner
Updated
Jun 6, 2011
Duration
7h 1m
Description

This course explains how to develop SQL Server Integration Services (SSIS) packages with an emphasis on the development of processes that support data warehousing.

It begins by describing the overall process of package development, then describes the types of tasks that Integration Services can perform and shows how these tasks can be performed sequentially or in parallel in the control flow by using precedence constraints.

Then the course continues by reviewing the data flow components that are used for extract, transform, and load processes. In addition, the course covers the Integration Services expression language and scripting, and demonstrates how to:

  • Debug packages
  • Configure logging
  • Manage transactions
  • Manage package restarts
It also describes how to automate the execution of packages.

The features and demonstrations in this SSIS training focus on the SQL Server 2008 R2 release, although most topics also apply to earlier versions of Integration Services.

Course FAQ
Course FAQ
Why do we use SSIS?

We use SSIS mostly for data warehousing purposes. It is very effective for data extraction, transformation, and loading (ETL). SSIS can also help automate SQL Server database maintenance, update data, and other useful functions.

What will I learn in this course?

You will learn:

  • how to develop Integration Services packages
  • data warehousing
  • how to use precedence constraints
  • how to extract, transform, and load processes
  • Integration Services expression language and scripting
  • how to debug packages
  • how to automate package execution
  • and much more
What version of software do I need?

This course and its concepts are based on the SQL Server 2008 R2 release, but most of the topics can apply to earlier or newer versions as well.

Are there prerequisites to this course?

Nope! This course is for beginners and assumes no previous training or exposure to SQL Server Integration Services (SSIS). The course is, however, intended for database professionals and/or business intelligence developers or those aspiring to be such.

Is SSIS free with SQL Server?

When you purchase a SQL Server license, a free SQL Server Integration Services license is included. If you don't have a SQL Server license then pricing ranges quite a bit depending on if you use Express and Developer editions, or Enterprise.

Is SSIS worth learning?

It may be a bit difficult, and some things that seem simple are not always so, but yes, it is worth learning! Especially if you are building complicated ETL packages across different systems, and/or if you work in a Microsoft shop.

About the author
Stacia Misner Varga
About the author

Stacia Misner Varga is a consultant, instructor, author, mentor, BI Partner for SQLSkills, and principal of Data Inspirations specializing in Microsoft business intelligence technologies for over 10 years. She is a frequent speaker at the PASS Summit, IT/Dev Connections conferences, and various SQL Saturday and Microsoft-related events worldwide.

More from the author
Designing SSIS Integration Solutions
Beginner
2h 41m
Jun 18, 2020
Securing SSAS Models
Advanced
1h 40m
Apr 10, 2020
Tuning SSAS Models
Advanced
3h 3m
Mar 10, 2020
More courses by Stacia Misner Varga

You're the smartest person in the room

PROVE IT

Your team won't just close the skills gap

THEY'LL HURDLE IT