Add'l Libraries

Microsoft SQL Server 2005 Integration Services

with Chris Randall

Course Description: This course will enable technology professionals with little or no ETL experience and minimal exposure to SQL Server 2005 and Visual Studio 2005 to be comfortable and productive with the SSIS tools and technologies. In this course you will learn about the SQL Server Management Studio (SSMS) and building Database Maintenance Plans. You will be introduced to the Business Intelligence Development Studio (BIDS) and working with Control Flow and Data Flow. 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.

10+ hours of media runtime

Tour of SSIS

Understand SSIS

Overview: ETL

SSIS Package Parts

Package Lifecycle

Tools for Building Pkgs

Export Data to Excel

Launch DTS Wizard

Choose a Data Source

Choose a Destination

Select the Data

Select Source Tables/Views

Edit Package Mappings

Execute the Package

Save the Package

Set Pkg Protection Level

Perform the Export

View Steps Performed

View Export Results

Import Data into Table

View Import Results

Run the Package

Introduction to BIDS

Pkgs in BIDS

BIDS Interface

SSIS Components

Control Flow Designer

Data Flow Designer

Event Handler Designer

Package Explorer

Connection Managers

Demo: BIDS Guided Tour

Execute Pkg Outside BIDS

Demo: DTExecUI

Demo: SQL Agent Job

Demo: View Pkgs in SSMS

Intro to Control Flow

Overview: Control Flow

Control Flow Tasks

Workflow Tasks

Execute SQL Task

Demo: Execute SQL Task

Control Flow Toolbox

Edit Task Properties

Create New Connection

Set SQL Source Type

Test Execute SQL Task

File System Task

Demo: File System Task

FTP Task

Send Mail Task

SQL Operations Tasks

Precedence Constraints

Overview

Constraint Values

Demo: Create Constraints

Data Processing Tasks

Data Flow Task

Things to Consider

Data Flow

Overview: Data Flow

Data Flow Pipeline

Data Flow Components

Data Sources

Source Adapters

Setup a Data Source

Data Flow Sources

Examine OLE DB Source

Data Destinations

Destination Adapters

Data Viewers

Setup Data Destination

Examine DataReader

Setup a Transformation

Examine Derived Column

DataReader Adv Props

Test the Package

Configure Data Viewer

Save/Run Package

Add Second Viewer

Data Transformations

Overview

DT Categories

Row Transformations

RowSet Transformations

Split/Join Transformations

BI Transformations

Other Transformations

Demo: Transformations

Demo: Union All Tfms

Demo: Sort Tfms

Demo: Audit Tfms

Test the Package

View Results

Demo: Multicast Tfms

Demo: Tfms

Variables and Config

Overview: Variables

Variable Properties

Common Data Types

Where to Use Variables

Variable Use Guidelines

Variable Scope

Scope w/Execute Package

Creating Variables

Demo: Add Variables

Use Vars in Control Flow

Demo: ForEach Enumerator

Use vars in Data Flow

Demo: ForEach w/Data Flow

Add an Expression

Property Expressions

Overview

Understand Configurations

Data Flow Expressions

Using Configurations

Config Stored as Strings

Package Config Organizer

Enable Package Config

Var/Config Between Pkgs

Parent/Childs Scenarios

Direct Configuration

Indirect Configuration

Enable Indirect Config

Advanced Control Flow Tasks

Overview

Using Containers

Container Properties

Grouping

Demo: Grouping

Task Host Container

Sequence Container

For Loop Container

ForEach Loop

Demo: ForEach Loop

Transaction Support

Demo: Transaction Support

Advanced Control Flow (cont)

Execute DTS Package

Demo: Execute DTS Pkg

DTS Designer Components

Execute Process

FTP Task

Web Service Task

Demo: Web Service Task

Examine Web Service

Examine the .wsdl

Create WS Package

Add a WS Task

Add a Script Task

Test Web Service Task

Checkpoints

Overview: Checkpoints

Implement Checkpoints

Considerations

Demo: Checkpoints

Error Handling/Logging

Package Logging

View Log Events in BIDS

Log Events Pane

Log Schema

Config Package Logging

Config Persistent Logging

Test Persistent Logging

Config SQL Server Logging

Examine dbo.sysdtslog90

View Windows Event Log

Custom Logging

Error Handling/Logging (cont)

Control Flows

Use Precedence

Demo: Precedence

Event Handling

Use Event Handling

Error Handler

Demo: Error Handler

Outputs in Data Flows

Config Error Output

Redirect Error Output

Redirect and Repair

Demo: Error Output

Advanced Data Flow

Sync Transformations

Async Transformations

Common Sync Types

Common Async Types

Error Outputs

Demo: Error Outputs

Advanced Transformations

Overview

Audit

Multicast

Conditional Split

Derived Column

Union All

Merge

Lookup

Demo: Lookup

Setup Properties

Fuzzy lookup

Demo: Fuzzy Lookup

Fuzzy Lookup Properties

Package Deployment

Challenges

What Can Help?

Overview: Deploy Pkgs

Package Deployment Utility

Deployment Utility Props

Create Deployment Folder

Deployment Folder

Deployment Manifest

Package Deployment Wizard

Deploy to File System

Deploy to SQL Server

Redeploy a Package

Package Management

Overview

Manage Pkgs w/DTUtil

Manage Pkgs w/SSMS

Executing Packages

Execute Package Utility

Demo: DTExecUI

DTExec

Scheduling w/SQLAgent

Demo: SQLAgent Jobs

Package Security

Overview: Security

Sensitive Package Data

Package Properties

Pkg Security Options

Package Storage

Store Pkgs in SSMS

SQL Server Pkg Security

Authorize SSIS Users

Demo: Authorize User

Sign Pkgs w/Certs

Control Flow Scripting

Extend SSIS Capabilities

Overview: Scripting

Custom Component Dev

SSIS Scripting

What Scripting Can Do

When to Use It?

When NOT to Use It?

Script Editor

Create a Script Task

SSIS Object Model

Script in Control Flow

Overview: Script Task

Overview: Variables

More Complex Script Task

Data Flow Scripting

Beyond Built-in Tasks

Scripting in Data Flows

Script Component

Input/Output Columns

Component as Source

Comp. as Destination

Comp. as Transformation

Beyond Built-In Data Flow

Create Custom Trans

Add Script Component

Edit Script Component

Build the Script

Row Count Transformation

Test Custom Trans

Add Logging

Test Logging

Custom SSIS Components

Custom Components

Component Development

3rd Party Components

Work w/3rd Party Component

Beyond ETL

Migrate SQL Servers

Transfer Tasks

Transfer Database Task

Transfer Logins Task

Maintenance Plans

Build Maintenance Plan

Export Pkg to a File

Additional Topics

Work with SSAS

Folder/File Mgmt

Work with Event Log

Demo: Work with WMI

Best Practices

Overview: Best Practices

Development

Demo: Annotations

Demo: Templates

Package Design

Error Handling

Package Design (cont)

Data Flow Sources

Data Flow Trfms

Demo: Block vs. Non-Block

Data Flow Trfms (cont)

Data Flow Dest

Data Flow Engine

Performance

Demo: Parent/Child Pkgs

Stopped Here

Demo: Parent Variables

Ongoing Development

Security

Management/Maintenance

Demo: Perfmon

Get our entire learning library

ONLY

$99

.99

per year

Visual Studio, ASP.NET, SQL, SharePoint and more...

3,400+ video tutorials
Microsoft MVP's and experts
Future video releases FREE
100% money-back guarantee

Not ready to buy? Try now

Need to train a team?

Our enterprise solution may be your best option.
Check out LearnNowPlus

Toll free: 1-877-960-8132 | Email: info@learnnowonline.com | 10250 Valley View Road Suite 121 | Eden Prairie, MN 55344

Follow Us

Microsoft SQL Server 2005 Integration Services

Get our entire learning library

ONLY

Visual Studio, ASP.NET, SQL, SharePoint and more...

Features

Support

About

Community