SQL Server 2008 Integration Services - MSS330

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.

Course ID: MSS330
Duration: 3 days
Audience: SQL Server users
Prerequisites: None
Topics:

A Guided Tour of 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
  • Microsoft SQL Server 2008 Integration Services
  • 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
  • Microsoft SQL Server 2008 Integration Services
  • 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
 

Call Toll-free 877-245-6815 or 303-416-3056 to set up your IT Training today.