Course at a glance
Included in these subscriptions:
- Dev & IT Pro Video
- Dev & IT Pro Power Pack
| Release date | 10/16/2009 | |
| Level | Advanced | |
| Runtime | 12h 40m | |
| Closed captioning | N/A | |
| Transcript | N/A | |
| eBooks / courseware | Included | |
| Hands-on labs | Included | |
| Sample code | Included | |
| Exams | Included |
Course description
This course will enable technology professionals with little or no ETL experience to be comfortable and productive with the SSIS tools and technologies. In this course you will learn about the Business Intelligence Development Studio (BIDS) and working with Control and Data Flows to build workflows to extract, transform, and load data using a variety of data sources, transformations, and destinations. 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.
Prerequisites
This course assumes no prior knowledge of SQL Server Integration Services. This course does assume prior knowledge of SQL Server 2008 and the use of SQL Server Management Studio for development and administrative tasks. You should be able to create CRUD (create, retrieve, update, and delete) queries using T-SQL, understand basic relational databases design, run script files and diagnose problems that occur, and have experience building applications that access data stored in SQL Server. You must also know how to connect to an instance of SQL Server 2008 using the various connection dialog boxes in Management Studio and development tools.
Learning Paths
This course will help you prepare for the following certification and exam:
MCTS: SQL Server 2008, Business Intelligence Development and Maintenance
70-448: TS: Microsoft SQL Server 2008, Business Intelligence Development and Maintenance
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
Module 1
Understanding SSIS (26:32)
- Introduction (03:14)
- What is ETL? (02:38)
- Integration Services Packages (03:52)
- Tools for Building IS Packages (03:35)
- IS Package Storage Options (01:25)
- Demo: Import-Export Wizard (09:37)
- Other Ways to Launch Wizard (01:44)
- Summary (00:24)
BIDS (18:49)
- Introduction (00:43)
- Demo: BIDS (06:21)
- Demo: Control Flow Tab (03:42)
- Demo: Data Flow Tab (04:36)
- Demo: Event Handler Tab (01:13)
- Demo: Package Explorer Tab (01:35)
- Summary (00:36)
Building-Executing a Package (24:01)
- Introduction (00:38)
- Demo:BIDS-New Project (03:06)
- Demo:Data Source Wizard (01:19)
- Demo: Package Design (08:32)
- Executing a Package in BIDS (02:53)
- Demo:Executing a Package (02:35)
- A Package Outside of BIDS (02:22)
- Demo:Execute Utility (02:06)
- Summary (00:26)
Module 2
Control Flow (28:59)
- Introduction (00:55)
- Elements in a Control Flow (01:01)
- Tasks (01:17)
- Containers (01:23)
- Precedence Constraints (02:50)
- Control Flow Tasks (01:31)
- Workflow Tasks (01:01)
- Data Processing Tasks (01:10)
- Scripting Tasks (00:58)
- SQL Server Tasks (00:27)
- Analysis Services Tasks (00:16)
- Maintenance Tasks (01:23)
- Working with Workflow Tasks (00:28)
- The Execute SQL Task (02:36)
- Demo: Execute SQL Task (11:11)
- Summary (00:25)
Workflow and Constraints (33:36)
- Introduction (00:40)
- File System Task (03:22)
- Demo: File System Task (04:26)
- FTP Task (01:39)
- Demo: FTP task (03:26)
- Send Mail Task (02:07)
- Demo: Send Mail Task (02:38)
- Precedence Constraints (02:50)
- Why Use Constraints? (01:49)
- Implementing Constraints (00:59)
- More Options (02:33)
- Demo: Constraint Options (05:52)
- Summary (01:09)
Module 3
Data Flows (28:36)
- Introduction (01:41)
- Data Flow Components (02:29)
- Data Flow Pipeline (01:12)
- Data Flow Sources (01:53)
- Connection Manager (01:05)
- Demo: Data Flow Task (03:50)
- Data Flow Destinations (01:03)
- Destination Types (00:15)
- Data Viewers (02:52)
- Demo: Data Viewers (11:43)
- Summary (00:29)
Data Flow Transformations (26:06)
- Introduction (00:50)
- Data Flow Transformations (04:50)
- Demo: Transformations (19:47)
- Summary (00:38)
Module 4
Variables (18:14)
- Introduction (02:02)
- Variable Properties (03:27)
- Variable Data Types (02:13)
- Variable Scope (03:21)
- Where Can You Use Variables (01:01)
- Demo: Variables (05:13)
- Summary (00:53)
Control Flow Variables (17:35)
- Introduction (00:41)
- Execute SQL Task (00:55)
- File System Task (01:13)
- Constraint Expressions (00:37)
- Property Expressions (01:24)
- Flow Property Expressions (00:41)
- Send Mail Task (00:28)
- Foreach Loop Container (01:02)
- Script Task (00:54)
- Demo:Foreach Loop (03:59)
- Demo: Script Task (05:09)
- Summary (00:27)
Data Flow Variables (21:37)
- Introduction (00:39)
- Variables in Data Flow (02:33)
- Configurations (00:58)
- Using Configurations (01:08)
- Sample Path (01:19)
- Configuration Storage (02:44)
- Demo: Configurations (05:04)
- Variables + Configurations (03:49)
- Demo: Configuration Path (02:46)
- Summary (00:34)
Module 5
Containers (21:38)
- Introduction (01:26)
- Using Containers (01:02)
- Container Types (01:19)
- Container Properties (02:35)
- Task Host Container (01:01)
- Sequence Container (02:57)
- Foreach Loop Container (02:13)
- Demo: Foreach Loop (08:30)
- Summary (00:31)
For Loop Containers (16:28)
- Introduction (01:16)
- For Loop Container (01:14)
- Demo: For Loop Container (07:36)
- Grouping Container (01:47)
- Demo: Grouping Container (03:45)
- Summary (00:47)
Transaction Support (25:42)
- Introduction (02:16)
- Transaction Properties (03:27)
- Types of Transactions (01:27)
- Demo: Transactions (13:41)
- Demo: Sequence Container (04:23)
- Summary (00:25)
Module 6
Checkpoints + Handling Errors (34:35)
- Introduction (02:03)
- Checkpoints (02:09)
- Implementing Checkpoints (02:44)
- How It Works (01:22)
- Demo: Checkpoint (13:55)
- Errors and Debugging (01:15)
- Error Handling in Constraints (02:08)
- Demo: Handling in Constraints (08:28)
- Summary (00:27)
Outputs + Breakpoints (21:14)
- Introduction (01:43)
- Error Actions (01:44)
- Demo: Error Actions (09:23)
- Breakpoints (00:44)
- Demo: Breakpoints (07:12)
- Summary (00:26)
Package Logging (15:46)
- Introduction (01:06)
- Demo: Package Logging (04:53)
- Configuring Package Logging (01:06)
- Log Providers (01:41)
- Demo: Log Providers (04:23)
- Demo: Windows Event Log (02:00)
- Summary (00:35)
Module 7
Event Handling (23:35)
- Introduction (01:12)
- Event Handling (01:09)
- Using Event Handlers (01:44)
- Demo: Event Handler (19:00)
- Summary (00:29)
Advanced Data Flow (23:27)
- Introduction (01:16)
- Synchronous Transformations (01:40)
- Asynchronous Transformation (03:08)
- Using Advanced Transformations (06:24)
- Demo: Conditional Split (10:38)
- Summary (00:18)
Slowly Changing Dimensions (25:15)
- Introduction (01:40)
- Slowly Changing Dimensions (03:53)
- Demo: Changing Dimensions (18:02)
- Summary (01:38)
Module 8
Deploying Packages (24:30)
- Introduction (01:10)
- Deployment Steps (02:21)
- Deployment Challenges (03:30)
- Deployment-Sensitive (02:03)
- Utilities That Can Help (02:10)
- Create a Deployment Utility (01:49)
- Utility Properties (02:05)
- Deployment Folder (01:29)
- Deployment Manifest (01:21)
- Demo: Deployment (06:03)
- Summary (00:24)
Installing Packages (27:49)
- Introduction (00:53)
- Installation Wizard (03:05)
- Deploy to the File System (01:26)
- Demo:Package Installation (06:14)
- Deploy to SQL Server (02:03)
- Demo:Deploy to SQL Server (03:34)
- Using Management Studio (00:49)
- Demo:Using Management Studio (03:07)
- SQL Server or File System? (04:16)
- Summary (02:19)
Module 9
Managing Packages (24:13)
- Introduction (01:03)
- Managing IS Packages (00:22)
- Managing Packages with DTUtil (02:19)
- DTUtil Exit Codes (00:26)
- DTUtil Samples (01:54)
- With Management Studio (01:29)
- Demo:Manage Packages with MS (05:56)
- Executing Packages (02:00)
- DTExecUI (01:08)
- Demo: DTExecUI (03:47)
- DTExec (00:45)
- Demo: DTExec (02:38)
- Summary (00:20)
Packages and Security (30:23)
- Introduction (00:39)
- Demo:Schedule Packages (04:34)
- Integration Services Security (01:36)
- Integration Services Threats (01:00)
- Protecting Package Information (01:00)
- Sensitive Information (01:28)
- Protection Level (01:05)
- ProtectionLevel Property (01:32)
- Demo:ProtectionLevel (04:43)
- Controlling Packages in SQL (01:11)
- Integration Services Roles (02:33)
- Authorizing IS users in SQL (00:43)
- Demo:Security in SSMS (07:06)
- Summary (01:06)
Module 10
Scripting (41:14)
- Introduction (00:53)
- Integration Services Scripting (01:29)
- Adding Code to a Package (00:53)
- Custom Component (01:21)
- What Can Scripting Do? (01:36)
- When Should I Script? (01:58)
- When Should I not Script? (01:32)
- IS Object Model (03:45)
- VS Tools for Script Editor (01:24)
- Demo:Implementing Scripts (05:38)
- Scripting in Control Flows (00:55)
- Global Dts object (02:25)
- Variables (00:18)
- Variables Collection (00:55)
- Accessing Variables (02:20)
- VariableDispenser Object (02:37)
- Demo:Variables in Script Code (08:16)
- Beyond Built-In Tasks (02:23)
- Summary (00:26)
Data Flow Scripting (39:03)
- Introduction (01:51)
- Input and Output Columns (02:08)
- Script Component Types (00:27)
- Script Component as Source (01:51)
- Component as Destination (01:23)
- Component as Transformation (01:38)
- Demo:Transformation (10:07)
- Synchronous and Asynchronous (01:13)
- Built-in Synchronicity (01:15)
- Synchronicity in the Script (01:59)
- Types of Asynchronous (00:51)
- Building Asynchronous (01:28)
- Beyond Built-In Data Flow (01:48)
- Custom IS Components (01:11)
- Custom Component Develop (02:23)
- Demo: Custom Components (04:23)
- Third-Party IS Components (00:58)
- Demo: Third-Party Components (01:39)
- Summary (00:21)
Module 11
Design Best Practices (45:33)
- Introduction (01:35)
- Keep it Simple (00:55)
- Best Practices (01:13)
- Development Standards (02:20)
- Annotations (01:44)
- Naming Conventions (02:36)
- Package Templates (01:21)
- Source Control (01:28)
- Demo:Best Practices (06:53)
- Package Design Best Practices (00:40)
- Modular Design (02:38)
- Demo:Modular Package (11:56)
- Package Best Practices (03:13)
- Error Handling and Logging (04:39)
- Auditing ETL (01:46)
- Summary (00:30)
Module 12
Data Flow Best Practices (39:59)
- Introduction (00:59)
- Data Flow Source Performance (00:24)
- Limit Columns (02:29)
- Convert Only When Necessary (01:59)
- Make Use of the Database (01:51)
- Data Flow Transformations (05:12)
- Demo:Data Flow Best Practice (07:23)
- Data Flow Destinations (00:39)
- Tune for Bulk Loads (01:40)
- Data Flow Destinations Too (03:55)
- SSIS Engine Best Practices (02:42)
- Deployment and Management (00:27)
- Deployment Best Practices (01:34)
- Prepare the Package (01:37)
- Security Best Practices (00:39)
- Management Best Practices (01:09)
- Back Up Packages (01:56)
- Demo:Management Best Practices (02:20)
- Summary (00:56)
Maintaining SQL Server (18:39)
- Introduction (01:14)
- Migrating and Maintaining (00:41)
- SQL Server Transfer Tasks (03:31)
- Demo:Transfer Tasks (05:18)
- SQL Server Maintenance Plans (00:26)
- Maintenance Plan Tasks (01:00)
- Demo:PlanTasks (06:04)
- Summary (00:21)
Analysis Services and WMI (17:08)
- Introduction (00:35)
- Working with Analysis Services (02:18)
- Working with WMI (00:55)
- WMI Data Reader Task (01:42)
- WMI Event Watcher Task (01:27)
- Demo:Data and Event Watcher (09:27)
- Summary (00:40)