SQL Courses | SQL Training

Introduction

SQL Server Integration Services 2008/2012 is aimed at individuals who have requirements to implement SQL Server Integration Services (SSIS) to export and import data between mixed data sources, catering for simple exchanges and more complex requirements involving transformation of data before it reaches its destination.

This course includes Microsoft SQL Server Visual Studio Development Environment for design, creation and management of SSIS packages and work with Control and Data Flows to build workflows to extract, transform, and load data using a variety of data sources, transformations, and destinations.

Individuals 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 is recommended for those working in both SQL Server 2008 and SQL Server 2012 Environments.

Pre-Requisites

• No prior knowledge of SQL Server Integration Services is needed.
• Prior knowledge of SQL Server 2008 or SQL Server 2012 Database Environments and the use of SQL Server Management Studio for development and administrative tasks are recommended.
• Individuals should be familiar with Database Querying using SQL.

Course Outline

Introduction to SQL Server Integration Services (SSIS)

• Architecture
• Features
• Tools
• Terminology

The SSIS Tools

• Import & Export Wizard
• Business Intelligence Development Studio (BIDS)
• Creating a Package
• The BIDS Screen Anatomy
• Packages
• Control Flow
• Connection Managers
• Variables
• Data Flow
• Event Handlers
• Package Explorer
• Executing A Package
• Installing A Package

SSIS Control Flow Tasks

• Task Toolbox
• SSIS Task Objects
• Looping & Sequence Tasks
• Scripting Tasks
• Analysis Services Tasks
• Data Flow Tasks
• Data Preparation Tasks
• Database Tasks
• Workflow Tasks
• Database Administration Tasks

SSIS Control Flow Containers

• Sequence Containers
• Group Containers
• For Loop Containers
• Foreach Loop Containers

SSIS Data Flow

• Sources: OLE DB, Excel, Flat File, Raw File, XML, ADO.NET
• Transformations: Aggregates, Audit, Derived Column, Data Conversion, Lookup, Conditional Split, Union, Merge, Script, and more
• Destinations: OLE DB, Excel, Flat File, Raw File, DataReader, SSAS Cube
• Data Viewers

Expressions & Variables

• Expression Overview
• Variable Overview
• Data Types: SSIS, Database, .NET
• Expression Builder
• Operators
• Functions

Joining Data In Data Flows

• Lookup
• Relational Join
• Merge Join
• Advanced Lookup Features

Scripting in SSIS

• VSTA
• VB or C#
• Control Flow Script Task
• Data Flow Script Component
• Accessing Package Variables From Scripts
• Connecting to Data Sources From Scripts
• Raising Events
• Logging Task Progress
• Coding, Debugging and Troubleshooting Scripting

Reliability & Scalability

• Restarting Packages
• Package Transactions
• Error Outputs

Error & Event Handling

• Precedence Constraints
• Event Handling
• Breakpoints
• Error Rows
• Logging

Advanced Programming Overview

• Creating Data Flow Components: Source, Transformation, Destination
• Creating New Tools for Toolboxes

Administering SSIS

• Package Configuration
• Deployment Utility
• Package Store
• Management Studio
• DTExecUI
• DTExec
• Security
• Scheduling A Package
• Proxy Accounts
• 64 Bit Issues