SQL Server Integration Services: Hands-On

You Will Learn How To

Migrate and transform data using SQL Server Integration Services (SSIS)
Upgrade DTS packages and perform imports and exports
Assemble tasks to perform complex data migrations
Enhance package functionality with scripting
Extract, Transform and Load (ETL) data
Integrate Business Intelligence components

Course Benefits

In order to improve business intelligence capabilities, organisations must effectively and securely manage data migration across a myriad of platforms. In this course, you gain the skills to automate complex migration tasks and audit the success or failure of migration processes. You learn to solve data management problems by creating dynamic packages for migrating, processing, and reporting on data for business intelligence.

Who Should Attend

SQL Server developers, administrators and analysts who need to migrate and transform data among diverse platforms. A basic knowledge of databases, SQL and scripting is helpful.

Hands-On Training

During this course, you perform extensive hands-on exercises that provide in-depth experience. Exercises include:

Upgrading packages with the DTS Migration Wizard
Converting data among heterogeneous sources: Oracle, Access and others
Manipulating package variables with scripting
Handling errors and managing exception rows
Administering the storage, security and execution of packages
Writing data to multiple destinations with multicasting

Course Content

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

<< Back to SQL Server Course List

Related Courses

SQL Server is a registered trademark of Microsoft Corporation.

SQL Server Courses

SQL Server® Integration Services: Hands-On