SQL Server 2008 Integration Services

Request more details:

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.
A Guided Tour of Integration Services:
Understanding Integration Services
What is ETL?
Integration Services Packages
Tools for Building Integration Services Packages
Integration Services Package Storage Options
Creating a Package with the Import and Export Wizard
Other Ways to Launch the Import and Export Wizard
Exploring and Executing an Integration Services Package in BIDS
The BIDS Interface and Components
Executing a Package in BIDS
Exploring and Executing a Package Outside of BIDS
Control Flow:
Overview of Control Flow in Integration Services
Elements in a Control Flow
Control Flow Tasks
Working with Workflow Tasks
The Execute SQL Task
File System Task
FTP Task
Send Mail Task
Precedence Constraints
Why Use Precedence Constraints?
Implementing Precedence Constraints
Recommendations
Data Flows:
The Data Flow Task
Data Flow Pipeline
Data Flow Sources
Data Flow Destination
Data Viewers
Data Flow Transformations
Row Transformations
Rowset Transformations
Split and Join Transformations
Business Intelligence Transformations
Other Transformations
Variables and Configurations:
Understanding Variables
Variable Properties
Variable Data Types
Variable Scope
Where Can You Use Variables?
Scope and the Execute Package Task
Using Variables in Control Flow
Execute SQL Task
File System Task
Precedence Constraint Expressions
Property Expressions
Send Mail Task
Foreach Loop Container
Script Task
Using Variables in Data Flow
Connection Managers
Derived Column Transformation
Conditional Split Transformation
Parameters
Configurations
Using Configurations
Configuration Storage
Using Variables and Configurations Between Packages
Parent and Child Scenarios
Direct Configuration
Indirect Configuration
Advanced Control Flow:
Advanced Control Flow Overview
Using Containers
Container Properties
Task Host Container
Sequence Container
Foreach Loop Container
For Loop Container
Grouping Container
Transaction Support in Integration Services
Error Handling and Logging:
When Things Go Wrong
Checkpoints
Handling Errors and Debugging
Error Handling in Control Flows with Precedence Constraints
Error Outputs in Data Flows
Breakpoints
Package Logging
Configuring Package Logging
Logging to a Text File
Logging in Windows Event Log
Custom Logging
Event Handling
Advanced Data Flow:
Synchronous Transformations
Asynchronous Transformations
Using Advanced Transformations
Audit Transformation
Multicast Transformation
Derived Column Transformation
Union All Transformation
Merge Transformation
Lookup Transformation
Fuzzy Lookup Transformation
Conditional Split Transformation
Handling Slowly Changing Dimensions
Loading a Data Warehouse
The Slowly Changing Dimension Transformation
Package Deployment:
Deploying Packages
Deployment Challenges
Create a Package Deployment Utility
Deployment Utility Properties
Deployment Folder
Deployment Manifest
Installing a Package
The Package Installation Wizard
Deploy a Package to the File System
Deploy a Package to SQL Server
Importing a Package Using Management Studio
Should You Deploy to SQL Server or the File System?
Redeploying Updated Packages
Package Management:
Overview of Package Management
Managing Integration Services Packages
Managing Package with DTUtil
Managing Packages with Management Studio
Executing Packages
DTExecUI: The Execute Package Utility
Executing Packages at the Command Line with DTExec
Scheduling Package Execution with SQL Server Agent
Integration Services Security
Protecting Sensitive Package Information
Controlling Access to Packages Stored in SQL Server
Signing Packages with Digital Certificates
Scripting and Custom Components:
Extending Integration Services Capabilities Through Code
Integration Services Scripting
Custom Component Development
Integration Services Scripting
Integration Services Object Model
Visual Studio Tools for Applications Script Editor
Scripting in Control Flows with the Script Task
Variables
Going Beyond Built-In Tasks
Scripting in Data Flows with the Script Component
Input and Output Columns
Script Component Types
Synchronous and Asynchronous Transformations
Going Beyond Built-In Data Flow Components
Custom Integration Services Components
Custom Component Development
Third-Party Integration Services Components
Best Practices:
Best Practices for Using Integration Services
Keep it Simple
Best Practices for Package Development and Design
Development Standards
Package Design Best Practices
Error Handling and Logging
Data Flow Best Practices
Data Flow Transformation Performance, Maintenance, and Ease of Use
Data Flow Destinations
Integration Services Engine Best Practices
Deployment and Management Best Practices
Deployment Best Practices
Security Best Practices
Management Best Practices
Going Beyond ETL:
Using Integration Services Beyond ETL
Migrating and Maintaining SQL Servers with Integration Services
SQL Server Transfer Tasks
SQL Server Maintenance Plans
Working with Analysis Services
The Analysis Services Processing Task
Dimension and Partition Processing Destinations
Analysis Services Execute DDL Task
Data Mining Query Task
Slowly Changing Dimension Transformation
Working with Windows Management Instrumentation
The WMI Data Reader Task
The WMI Event Watcher Task