SQ-320 - Advanced Microsoft SQL Server 2008 Integration Services R2 (SSIS)
Course
SQ-320
Price:
$1,895.00
Software Assurance Value:
Not Eligible
Duration:
2 Days
Advanced SQL Server 2008 R2 Integration Services
Description
This two-day SSIS course focuses on advanced concepts for
both managing and applying SSIS in an enterprise environment. Just knowing how
to use SSIS is often not enough when faced with challenging processing
requirements or complicated environments. This course takes IT professionals,
DBAs and developers to a deeper level of SSIS skills maturity. By the end of the
course, students will have the knowledge on how to architect SSIS solutions and
deal with advanced requirements for data cleansing, data warehouse ETL, dynamic
project environments, file management, and package optimization.
Return to Top
Audience
This course is intended for solution architects, DBAs, and
developers who work with SSIS and are responsible for ETL or DBA activities
related to data processing, data architecture planning, or SSIS administration.
Return to Top
Prerequisites
Before attending this course, it is recommended that
students have the following skills:
- Hands on SSIS Development Skills
Return to Top
Course Objectives
Upon completion of this course, the student will be able
to:
- Plan ETL architecture and infrastructure
- Understand advanced data cleansing
- Apply data warehouse ETL requirements in SSIS
- Create SSIS solution frameworks and design
advanced dynamic configurations
- Troubleshoot and optimize SSIS packages
Return to Top
Course Outline
Day 1
Module 00: Course Overview – Advanced SSIS
Module 1: SSIS Enterprise Architecture Planning
- ETL solution planning including infrastructure and server considerations, Integration with disparate systems and dependencies, and server execution impact
- TSQL vs. SSIS considerations in SSIS package design
Module 2: File Management and Third Party Components
- File manipulation and import components: FTP Task, XML Task, Bulk Insert Task, File System Task, For Each Loop Container, Flat File Source, Import and Export Transformations
- Third-party component review and use
Lab: Building an FTP Solution
Module 3: Advanced Data Extraction and Loading
- Source adapters, data extraction practices including incremental extraction, destination adapter and data loading optimization techniques
- Partition table management
Lab: Advanced Data Sources and Destinations
Module 4: Data Quality and Cleansing
- Profiling source data, applying the Fuzzy Lookup and Fuzzy Grouping Transformations and internals, text parsing
Lab: Data Cleansing
Day 2
Module 5: Advanced Deployment, Configurations and Dynamic Expressions
- Planning an enterprise configuration environment and solving configuration challenges
- Storage, Security, and Configuration Decisions
Module 6: Data Warehouse ETL
- Dimension table processing and loading, building a custom SCD, inferred member data handling
- Fact table package design, handling fact inserts and updates, changing data grain, Analysis Services processing
Lab A: Dimension Table ETL
Lab B: Fact Table ETL
Module 7: Team Development, Package Design and Frameworks
- Team development environment, source control, and designing modular packages
- Creating package frameworks for administration
Module 8: SSIS Troubleshooting and Optimization
- Identifying package bottlenecks, understanding the SSIS data flow architecture, monitoring Data Flow executions with pipeline logging,
- Debugging tasks with breakpoints, SSIS performance monitor counters and tracking
Lab: Setting Breakpoints and Using Performance Monitor
Return to Top
