ETL Solutions Using Microsoft SQL Server 2005 Integration Services

Course Outline

• What's New in SQL Server 2005 SSIS
• Architecture – Packages, Tasks, Data Source Elements, Data Source Views
• Precedence Constraints
• Containers
• Variables
• Data Flow Elements
• Error Handling and Logging
• Editions of SQL Server 2005

• Using the Import and Export Wizard
• Package Installation Wizard
• Business Intelligence Development Studio
• Creating Your First Package
• The Solution Explorer Window
• The SSIS Package Designer

• Shared Properties
• Using the Bulk Insert and Execute SQL Tasks
• Data Flow Task
• Execute Process Task
• Using the File System and FTP Task
• Execute Package Task
• Script and ActiveX Tasks
• Send Mail Task
• Message Queue Task
• Web Service Task
• WMI Data Reader and Event Watcher Task
• XML Task
• SQL Server Analysis Services Execute DDL and Processing Tasks
• Data Mining Query Task
• The Expression Page

• Containers
• Task Host Containers
• Sequence Containers
• For Loop Container
• Foreach Loop Container

• Sources
• OLE DB Source
• Excel Source
• Flat File Source
• Raw File Source
• XML Source
• Data Reader Source

• Destinations
• Data Mining Model Training
• DataReader Destination
• Dimension and Partition Processing
• Excel Destination
• Flat File Destination
• OLE DB Destination
• Raw File Destination
• Recordset Destination
• SQL Server and Mobile Destinations

• Transformations
• Aggregate
• Audit
• Character Map
• Conditional Split
• Copy Column
• Data Conversion
• Data Mining Query
• Derived Column
• Export Column
• Fuzzy Grouping and Lookup
• Import Column
• Lookup
• Merge
• Merge Join
• Multicast
• OLE DB Command
• Percentage and Row Sampling
• Pivot and Unpivot
• Row Count
• Script Component
• Slowly Changing Dimension
• Sort
• Term Extraction and Lookup
• Union All
• Data Viewers

Advanced Tasks and Transforms

• Using Variables & Expressions
• Using SQL Output Parameters to Change Runtime Settings
• Import Column Example Using File Iteration
• Using Temp Tables in SSIS Package Development
• Export Column
• Row Count
• OLE DB Command
• Term Extraction
• Term Lookup
• Fuzzy Lookup
• Fuzzy Grouping
• Pivot Transform
• Unpivot
• Slowly Changing Dimension
• Database Object-Level Tasks

• Scripting Overview
• Expressions
• Script Tasks
• Using .NET Assemblies
• Structured Exception Handling
• Using the Script Component
• Debugging the Script Component

• Excel
• Access
• Oracle
• The Currency Conversion Service
• XML Data

• Restarting Packages
• Simple Control Flow
• Containers within Containers and Checkpoints
• Variations on a Theme
• Inside the Checkpoint File
• Package Transactions
• Error Outputs
• Scaling Out Memory Pressures & by Staging Data

• The Integration Services Engine: An Analogy
• Understanding the SSIS Data Flow and Control Flow
• Enterprise Workflows with the Control Flow
• Enterprise Data Processing with the Data Flow
• Memory Buffer Architecture
• Types of Transformations
• Advanced Data Flow Execution Concepts

• Migrating DTS 2000 Packages to SSIS
• Using the Package Migration Wizard
• Running DTS 2000 Packages under SSIS Package
• Metadata and Storage Management
• Managing SSIS Packages within SQL Server Management Studio
• Managing DTS 2000 Packages within SQL Server Management Studio

• Precedence Constraints
• Precedence Constraints and Expressions
• Multiple Constraints Event Handling
• Events
• Inventory Example
• Event Bubbling
• Breakpoints, Checkpoints, & Logging

• Building and Debugging the components
• External management and WMI task Implementation
• Package Configuration
• SSIS Software Development Life Cycle
• Case Study

Back to Course Description   Onsite Training   Class Schedule