Learn your way! Get started

SQL Server 2005: Integration Services

with expert Chris Randall

Course at a glance

Included in these subscriptions:

Dev & IT Pro Video
Dev & IT Pro Power Pack

	Release date	5/23/2008
	Level	Advanced
	Runtime	10h 58m
	Closed captioning	N/A
	Transcript	N/A
	eBooks / courseware	Included
	Hands-on labs	Included
	Sample code	Included
	Exams	Included
	Platform	Internet Explorer Only

Enterprise Solutions

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

Course description

This course will enable technology professionals with little or no ETL experience and minimal exposure to SQL Server 2005 and Visual Studio 2005 to be comfortable and productive with the SSIS tools and technologies. In this course you will learn about the SQL Server Management Studio (SSMS) and building Database Maintenance Plans. You will be introduced to the Business Intelligence Development Studio (BIDS) and working with Control Flow and Data Flow. You will also become familiar with SSIS package management and package deployment along with learning to write solid code using debugging, error handling and logging techniques.

Meet the expert

Chris Randall specializes in SQL Server development and administration including Microsoft's Business Intelligence toolset. Chris holds several industry certifications. He is a courseware author, developer, database administrator, consultant, and speaker at industry conferences.

Course outline

Module 1

Tour of SSIS (38:24)

Introduction (03:26)
Understand SSIS (01:08)
Overview: ETL (01:38)
SSIS Package Parts (01:10)
Package Lifecycle (02:24)
Tools for Building Pkgs (01:28)
Export Data to Excel (00:09)
Launch DTS Wizard (01:56)
Choose a Data Source (01:27)
Choose a Destination (01:12)
Select the Data (02:10)
Select Source Tables/Views (01:18)
Edit Package Mappings (01:03)
Execute the Package (00:29)
Save the Package (00:24)
Set Pkg Protection Level (02:07)
Perform the Export (00:33)
View Steps Performed (01:24)
View Export Results (02:12)
Import Data into Table (05:57)
View Import Results (00:51)
Run the Package (03:22)
Summary (00:28)

Introduction to BIDS (27:14)

Introduction (00:51)
Pkgs in BIDS (01:12)
BIDS Interface (01:56)
SSIS Components (00:33)
Control Flow Designer (01:48)
Data Flow Designer (01:24)
Event Handler Designer (01:04)
Package Explorer (00:38)
Connection Managers (00:33)
Demo: BIDS Guided Tour (09:32)
Execute Pkg Outside BIDS (01:16)
Demo: DTExecUI (01:15)
Demo: SQL Agent Job (02:54)
Demo: View Pkgs in SSMS (01:44)
Summary (00:30)

Intro to Control Flow (24:27)

Introduction (00:48)
Overview: Control Flow (00:41)
Control Flow Tasks (01:11)
Workflow Tasks (00:57)
Execute SQL Task (00:56)
Demo: Execute SQL Task (02:43)
Control Flow Toolbox (00:39)
Edit Task Properties (01:14)
Create New Connection (00:29)
Set SQL Source Type (06:14)
Test Execute SQL Task (01:11)
File System Task (00:53)
Demo: File System Task (02:35)
FTP Task (00:48)
Send Mail Task (00:56)
SQL Operations Tasks (01:47)
Summary (00:19)

Module 2

Precedence Constraints (13:33)

Introduction (00:43)
Overview (01:30)
Constraint Values (02:48)
Demo: Create Constraints (04:12)
Data Processing Tasks (01:49)
Data Flow Task (00:25)
Things to Consider (01:40)
Summary (00:24)

Data Flow (37:31)

Introduction (00:49)
Overview: Data Flow (01:44)
Data Flow Pipeline (01:05)
Data Flow Components (00:44)
Data Sources (00:53)
Source Adapters (00:36)
Setup a Data Source (01:56)
Data Flow Sources (00:36)
Examine OLE DB Source (03:49)
Data Destinations (02:52)
Destination Adapters (01:34)
Data Viewers (01:38)
Setup Data Destination (01:44)
Examine DataReader (01:03)
Setup a Transformation (00:50)
Examine Derived Column (04:04)
DataReader Adv Props (01:04)
Test the Package (01:23)
Configure Data Viewer (02:30)
Save/Run Package (02:19)
Add Second Viewer (03:40)
Summary (00:29)

Data Transformations (28:12)

Introduction (01:03)
Overview (01:12)
DT Categories (00:51)
Row Transformations (02:08)
RowSet Transformations (02:30)
Split/Join Transformations (03:33)
BI Transformations (01:30)
Other Transformations (01:28)
Demo: Transformations (01:06)
Demo: Union All Tfms (01:29)
Demo: Sort Tfms (02:20)
Demo: Audit Tfms (03:46)
Test the Package (00:21)
View Results (01:03)
Demo: Multicast Tfms (00:15)
Demo: Tfms (02:48)
Summary (00:42)

Module 3

Variables and Config (34:42)

Introduction (00:43)
Overview: Variables (02:05)
Variable Properties (01:47)
Common Data Types (00:31)
Where to Use Variables (02:13)
Variable Use Guidelines (01:56)
Variable Scope (01:22)
Scope w/Execute Package (01:06)
Creating Variables (01:09)
Demo: Add Variables (03:31)
Use Vars in Control Flow (03:14)
Demo: ForEach Enumerator (05:03)
Use vars in Data Flow (01:42)
Demo: ForEach w/Data Flow (02:42)
Add an Expression (04:54)
Summary (00:35)

Property Expressions (26:16)

Introduction (00:49)
Overview (02:09)
Understand Configurations (00:56)
Data Flow Expressions (01:52)
Using Configurations (02:29)
Config Stored as Strings (02:48)
Package Config Organizer (00:42)
Enable Package Config (05:42)
Var/Config Between Pkgs (00:58)
Parent/Childs Scenarios (01:23)
Direct Configuration (00:59)
Indirect Configuration (00:58)
Enable Indirect Config (03:45)
Summary (00:39)

Module 4

Advanced Control Flow Tasks (39:05)

Introduction (00:38)
Overview (01:21)
Using Containers (02:44)
Container Properties (02:26)
Grouping (01:00)
Demo: Grouping (01:38)
Task Host Container (01:10)
Sequence Container (00:48)
For Loop Container (01:29)
ForEach Loop (02:10)
Demo: ForEach Loop (10:51)
Transaction Support (04:19)
Demo: Transaction Support (07:44)
Summary (00:43)

Advanced Control Flow (cont) (25:35)

Introduction (00:45)
Execute DTS Package (00:57)
Demo: Execute DTS Pkg (03:31)
DTS Designer Components (02:42)
Execute Process (00:32)
FTP Task (00:33)
Web Service Task (01:01)
Demo: Web Service Task (00:26)
Examine Web Service (02:02)
Examine the .wsdl (01:34)
Create WS Package (02:10)
Add a WS Task (05:38)
Add a Script Task (02:17)
Test Web Service Task (00:59)
Summary (00:19)

Checkpoints (11:57)

Introduction (00:53)
Overview: Checkpoints (00:56)
Implement Checkpoints (02:09)
Considerations (01:37)
Demo: Checkpoints (05:51)
Summary (00:30)

Module 5

Error Handling/Logging (23:41)

Introduction (01:03)
Package Logging (02:11)
View Log Events in BIDS (00:31)
Log Events Pane (03:52)
Log Schema (01:01)
Config Package Logging (01:39)
Config Persistent Logging (04:04)
Test Persistent Logging (01:10)
Config SQL Server Logging (01:30)
Examine dbo.sysdtslog90 (02:42)
View Windows Event Log (02:14)
Custom Logging (01:00)
Summary (00:39)

Error Handling/Logging (cont) (26:08)

Introduction (01:39)
Control Flows (00:49)
Use Precedence (00:50)
Demo: Precedence (02:37)
Event Handling (03:08)
Use Event Handling (00:47)
Error Handler (00:28)
Demo: Error Handler (03:21)
Outputs in Data Flows (02:07)
Config Error Output (01:08)
Redirect Error Output (00:49)
Redirect and Repair (01:18)
Demo: Error Output (06:26)
Summary (00:34)

Advanced Data Flow (15:36)

Introduction (00:58)
Sync Transformations (01:14)
Async Transformations (01:38)
Common Sync Types (00:34)
Common Async Types (00:34)
Error Outputs (02:37)
Demo: Error Outputs (07:04)
Summary (00:54)

Advanced Transformations (29:55)

Introduction (00:48)
Overview (00:48)
Audit (00:58)
Multicast (01:07)
Conditional Split (00:59)
Derived Column (00:51)
Union All (01:13)
Merge (00:48)
Lookup (01:17)
Demo: Lookup (02:01)
Setup Properties (04:59)
Fuzzy lookup (02:16)
Demo: Fuzzy Lookup (03:23)
Fuzzy Lookup Properties (07:47)
Summary (00:34)

Module 6

Package Deployment (32:16)

Introduction (00:52)
Challenges (03:02)
What Can Help? (01:49)
Overview: Deploy Pkgs (01:35)
Package Deployment Utility (00:59)
Deployment Utility Props (01:18)
Create Deployment Folder (03:24)
Deployment Folder (01:22)
Deployment Manifest (00:55)
Package Deployment Wizard (07:30)
Deploy to File System (01:17)
Deploy to SQL Server (05:56)
Redeploy a Package (01:32)
Summary (00:39)

Package Management (24:07)

Introduction (00:56)
Overview (01:10)
Manage Pkgs w/DTUtil (01:06)
Manage Pkgs w/SSMS (01:33)
Executing Packages (00:58)
Execute Package Utility (01:44)
Demo: DTExecUI (05:27)
DTExec (01:41)
Scheduling w/SQLAgent (01:33)
Demo: SQLAgent Jobs (07:11)
Summary (00:43)

Package Security (30:49)

Introduction (01:00)
Overview: Security (01:13)
Sensitive Package Data (00:57)
Package Properties (01:52)
Pkg Security Options (07:38)
Package Storage (01:07)
Store Pkgs in SSMS (05:17)
SQL Server Pkg Security (01:44)
Authorize SSIS Users (00:40)
Demo: Authorize User (05:05)
Sign Pkgs w/Certs (03:23)
Summary (00:47)

Module 7

Control Flow Scripting (21:07)

Introduction (01:05)
Extend SSIS Capabilities (00:27)
Overview: Scripting (01:00)
Custom Component Dev (00:49)
SSIS Scripting (00:12)
What Scripting Can Do (00:32)
When to Use It? (00:56)
When NOT to Use It? (00:50)
Script Editor (00:25)
Create a Script Task (03:41)
SSIS Object Model (02:04)
Script in Control Flow (00:23)
Overview: Script Task (01:18)
Overview: Variables (01:26)
More Complex Script Task (05:26)
Summary (00:25)

Data Flow Scripting (33:21)

Introduction (01:17)
Beyond Built-in Tasks (01:31)
Scripting in Data Flows (01:06)
Script Component (00:31)
Input/Output Columns (00:56)
Component as Source (01:00)
Comp. as Destination (00:46)
Comp. as Transformation (00:31)
Beyond Built-In Data Flow (00:43)
Create Custom Trans (02:07)
Add Script Component (00:34)
Edit Script Component (02:01)
Build the Script (06:00)
Row Count Transformation (02:02)
Test Custom Trans (02:13)
Add Logging (08:17)
Test Logging (01:22)
Summary (00:16)

Custom SSIS Components (11:03)

Introduction (01:42)
Custom Components (01:16)
Component Development (00:55)
3rd Party Components (00:55)
Work w/3rd Party Component (05:44)
Summary (00:29)

Beyond ETL (32:57)

Introduction (01:13)
Beyond ETL (01:03)
Migrate SQL Servers (01:06)
Transfer Tasks (01:38)
Transfer Database Task (07:18)
Transfer Logins Task (03:30)
Maintenance Plans (01:51)
Build Maintenance Plan (08:27)
Export Pkg to a File (05:49)
Summary (00:59)

Module 8

Additional Topics (16:38)

Introduction (00:51)
Work with SSAS (01:26)
Folder/File Mgmt (00:51)
Work with Event Log (01:41)
Demo: Work with WMI (10:52)
Summary (00:54)

Best Practices (53:45)

Introduction (00:42)
Overview: Best Practices (00:32)
Development (05:12)
Demo: Annotations (02:49)
Demo: Templates (04:25)
Package Design (04:17)
Error Handling (02:18)
Package Design (cont) (04:00)
Data Flow Sources (02:01)
Data Flow Trfms (02:48)
Demo: Block vs. Non-Block (03:10)
Data Flow Trfms (cont) (00:49)
Data Flow Dest (02:11)
Data Flow Engine (01:35)
Performance (01:42)
Demo: Parent/Child Pkgs (00:42)
Stopped Here (01:03)
Demo: Parent Variables (03:38)
Ongoing Development (03:05)
Security (01:13)
Management/Maintenance (02:42)
Demo: Perfmon (02:09)
Summary (00:32)