SQL Server 2008 Integration Services Course

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.

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.

Topics covered on the 3 day SQL Server 2008 Integration Services course

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
• © ITCourseware, LLC 2
• 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
• © ITCourseware, LLC 3
• 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
• 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