Advanced SQL Server 2005 Integration Services (SSIS) for DBAs

Course: SSISA

Duration: 4 Days

Level: III

Course Summary

Designed mainly for SQL Server 2005 DBAs (and SSIS developers whose job description spans both SSIS Developer and DBA tasks), this scenario-oriented presentation concentrates on the more esoteric features of SSIS:

Deployment internals
Runtime engine
Data Flow engine
Execution trees
Parallelism
Source and destination adapters
Advanced transformations (synchronous vs. asynchronous)
Performance and tuning
Performance measurement
Partitioned tables (in data warehouses)
Partition maintenance
ETL planning and development

« Hide The Details

Topics Covered In This Course

REVIEW OF BI TERMS AND CONCEPTS

The data warehousing (DW) environment
The DW computing context
What is a data warehouse?
What is a data mart?
What is Business Intelligence (BI)?
How do OLTP and OLAP differ?
What is data mining?
Operational vs. historical data
Dimensional modelling
Some dimensional modelling characteristics
What is a star schema?
What is a snowflake schema?
What is metadata?

REVIEW OF SSIS BASICS

Architecture
Tools
Wizards
Packages
Control Flows
Data Flows
SSIS in operational/data warehousing environments
SSIS data types
SSIS transformation types
Containers
Precedence constraints
Variables
Expressions

REVIEW OF MICROSOFT BI FRAMEWORK

Relational DB
SQL Server Management Studio
SQL Profiler
Reporting Services (SSRS)
Analysis Services (SSAS)
Integration Services (SSIS)
Microsoft Office: Excel, etc.
PerformancePoint

CHANGES PER SQL SERVER 2005 SERVICE PACK 2 (SP2)

Analysis Services
Database engine
Integration Services
Replication Services
Reporting Services
Management Studio
Scripting
Copy DB Wizard

SSIS 64-BIT CONSIDERATIONS

Defined (e.g., 32-bit vs. 64-bit)
SSIS features installed
Tools installed
Message Queue Task
DTS packages
SSIS packages (e.g., running)
SQL Server Agent jobs

SSIS STORAGE ENGINE

Data flow vs. control flow
Package execution
Buffer architecture
Transformation types

RAW FILE SOURCE/DESTINATION

Why consider
Raw File Source adapter
Raw File Destination adapter
Staging area

DATA FLOW PERFORMANCE AND TUNING

Buffers optimisation
Pipeline execution optimisation
Logging optimisation
Use of indexes
Optimise inserts

COMPREHENSIVE ETL DESIGN AND DEVELOPMENT WORKSHOP

Top-level planning
Source-to-destination mapping
Data profiling
Load frequency
Historical vs. incremental loads
Extraction design
Staging area
Recovery considerations
Dimension table processing
Slowly Changing Dimensions (SCD)
Late arriving (inferred) dimensions
Fact table processing
Extracting fact updates and deletes
Transforming dimension/fact data
Fact data aggregation
Confirmed dimensions/facts
Surrogate key maintenance
Fact table load
Periodic vs. accumulating snapshot fact considerations
Audit considerations
Parent package/child package considerations
Event handling
Unit testing

DW PARTITIONING

When to partition
Benefits
How to design and create
Sliding window
Management advantage
Parallelism
Indexes
Partition switching archiving

SSIS PACKAGE MANAGEMENT

SSIS – the big picture
Integration Services service
Managing SSIS packages
Configuring the SSIS service
Importing and exporting packages
Backing up and restoring packages
Adding and removing packages
Deleting a package
Create an SSIS package template

SSIS TROUBLESHOOTING

Package design-time troubleshooting options
Control Flow troubleshooting
Data Flow troubleshooting
Package execution troubleshooting: after deployment
Script troubleshooting
Setting Breakpoints: task or container
Adding a Data Viewer: Data Flow
Viewing event loops: Event Viewer
Logging package execution
Execution trees

SSIS MAINTENANCE TASKS

Backup Database
Check Database Integrity
Execute SQL Server Agent Job
Execute T-SQL Statement
History Cleanup
Maintenance Cleanup
Notify Operator
Rebuild Index
Reorganize Index
Shrink Database
Update Statistics

ADVANCED DEPLOYMENT CONSIDERATIONS

Package configuration
What does the Deployment Wizard do?
When should the deployment utility be used vs. import/Export, XCopy, etc?
How to override configurations
How to deploy and account for security

SECURITY AND PACKAGE EXECUTION

Roles, protection levels and passwords
Package execution via command line
Automatic package scheduling

SSIS PERFORMANCE AND TUNING

Tools and techniques
Buffer usage
Buffer signing
Execution trees
Data transformation design alternatives
Parallelism
Performance measurement (e.g., performance counters, SQL Server Profiler)

What You Can Expect

Upon completion of this course, the delegate should be competent to develop, deploy, tune and source a diverse mix of complex SSIS package solutions.

Who Should Take This Course

SQL Server 2005 DBAs (and SSIS developers).

Recommended Prerequisites

Completion of our SQL Server 2005 Integration Services Fundamentals or equivalent knowledge.

Training Style

Lecture and hands-on.

« Hide The Details

Related Courses

Code	Course Title	Duration	Level
MSBI	Accelerated Microsoft Business Intelligence: From SQL 2008 through SharePoint and Office 2007	4 Days	I	Details
SSISF	SQL Server 2005 Integration Services Fundamentals	3 Days	II	Details
SSISD	Advanced SQL Server 2005 Integration Services (SSIS) for Developers	4 Days	III	Details

Every student attending a Verhoef Training class will receive a certificate good for $100 toward their next public class taken within a year.

You can also buy "Verhoef Vouchers" to get a discounted rate for a single student in any of our public or web-based classes. Contact your account manager or our sales office for details.

Schedule For This Course
There are currently no public sessions scheduled for this course. We can schedule a private class for your organization just a couple of weeks from now. Or we can let you know the next time we do schedule a public session.

Notify me the next time this course is confirmed!

Can't find the course you want?
Call us at 800.533.3893, or
email us at info@verhoef.com