Introduction to Integration Services

• Defining SQL Server Integration Services
• Exploring the need for migrating diverse data
• The role of Business Intelligence (BI)

SSIS Architecture and Tools

Managing heterogenous data

• Leveraging the Extract, Transform and Load (ETL) capabilities of SSIS
• Running wizards for basic migrations
• Creating packages for complex tasks

Illustrating SSIS architecture

• Distinguishing between data flow pipeline and package runtime
• Executing packages on the client side or hosted in the SSIS service

Upgrading legacy DTS

• Executing existing DTS packages in the SSIS environment
• Converting DTS packages to SSIS with the migration wizard
• Logging migration results

Implementing Tasks and Containers

Utilising basic SSIS objects

• Configuring connection managers
• Adding data flow tasks to packages
• Reviewing progress with data viewers
• Assembling tasks to perform complex data migrations

Operating system-level tasks

• Copying, moving and deleting files
• Transferring files with the FTP task
• Reading system information with WMI query language (WQL)

Communicating with external sources

• Sending messages through mail
• Detecting system events with WMI

Processing XML

• Iterating XML nodes
• Writing XML files from databases

Extending Capabilities with Scripting

Writing expressions

• Making properties dynamic with variables
• Building expressions in Expression Builder

Script Task

• Extending functionality with the Script Task
• Debugging, breakpoints, watches

Transforming with the Data Flow Task

Performing transforms on columns

• Converting and calculating columns
• Transforming with Character Map

Combining and splitting data

• Profiling data
• Merge, Union, Conditional Split
• Multicasting and converting data

Manipulating row sets and BLOB data

• Aggregate, sort, audit and look up data
• Importing and exporting BLOB data
• Redirecting error rows

Performing database operations

• Implementing Change Data Capture (CDC)
• Executing a SQL task
• Bulk inserting data from text files

Error Handling, Logging and Transactions

Organising package work flow

• Defining success, failure, completion and expression precedence constraints
• Handling events and event bubbling

Designing robust packages

• Choosing log providers
• Adapting solutions with package configurations
• Auditing package execution results

Administering Business Intelligence

Managing and securing packages

• Storing packages in Package Store and msdb
• Encrypting packages with passwords and user keys

Integrating with other BI components

• Displaying data in Reporting Services
• Accessing package data with ADO.NET