5-Day (BI-ISE2E08-301-EN)
During this 5-day instructor led training, you will learn the ins and outs of SSIS and how to apply its enterprise functionality to DBA data management and file handling operations as well as data warehouse ETL systems. At the completion of this course, you will understand how to design, develop, deploy, and operate SSIS solutions from source systems extractions, SSIS administration, data integration and transformation, and business intelligence system loading.
The volume and complexity of data is always increasing and data professionals need to know how to integrate, maintain, and process data for varying purposes. This workshop prepares the database professional with the skills needed for applying SQL Server 2005 & 2008 Integration Services (SSIS) to enterprise DBA and ETL tasks.
This course is intended for database professionals that are responsible for ETL or DBA activities related to data processing, data architecture planning, or SSIS administration.
The target audience for this session is IT professionals and developers who want to learn the details of how to use SSIS to accomplish data integration, data warehouse loading, and how to administer SSIS through the development lifecycle to production.
Before attending this course, it is recommended that students have the following skills:
- Experience working with databases and database administration
- No prior experience with SQL Server 2000 DTS or SQL Server 2005 or 2008 required
- Primary focus: SQL Server 2005 & 2008 Integration Services
- Integration with: SQL Server 2005 & 2008 Database Engine
- Integration with: SQL Server 2005 & 2008 Analysis Services
Upon completion of this course, the student will be able to:
- Create and develop new SSIS projects and packages
- Apply SSIS to file and data management
- Understand and Apply ETL Concepts in SSIS including dimensions and fact table ETL and loading SSAS dimension and cubes
- Administer SSIS for team deployment and production execution
Section A: SSIS Overview and Core Features
Module 01: SSIS Overview
- SQL Server 2005 & 2008 installation and tools, SSIS features and architecture, the role of SSIS for the DBA, Working with SSIS Projects, Properties windows and viewers, Data Sources, Data Source Views, Connections, Package variables, Properties windows and viewers, Data Sources, Data Source Views, Connections, Package variables.
- Lab 01: Creating an SSIS Project and Package
Module 02: SSIS Control Flow Objects and Features
- Control Flow Task review, Execute SQL Task and Parameters, Sequence Container, Looping Containers, Standard and Expression based Constraints
- Lab 02: Workflow in the Control Flow
Module 03: Applying Data Flow Transformations and Adapters
- Data Flow source and destination adapters, Data expressions and conversion transformations, Data association and correlation transformations (IE: Lookup, Merge), Data Paths and Data Viewers
- Lab 03: Working with Data in the Data Flow
Section B: Applying SSIS to Common DBA and ETL Tasks
Module 04-Xfer: SQL Server Integration Services Management Tasks
- Import and Export Wizards, SQL Server Maintenance Plans in Management Studio (SSMS), SQL Server Management Tasks in the Control Flow
- Lab 04-Xfer: Working with Transfer Tasks
Module 04-File: Working with and Importing Files
- Drilling into File manipulation components: FTP Task, XML Task, Bulk Insert Task, File System Task, For Each Loop Container
- Lab 04-File: Building an FTP Solution
Module 04-Extr: Data Extraction Methods and Destination Optimization
- Source Adapters, Data extraction practices, Destination Adapters, Data loading optimization
- Lab 04-Extr: Putting It Together: Control Flow, Data Flow and Data Extraction
Module 04-Data: Using Data Cleansing to Increase Data Quality
- Applying the Fuzzy Lookup and Fuzzy Grouping transformations, Parsing words from text columns, importing and exporting BLOB data
- Lab 04-Data: Data Cleansing
Module 04-Dim: Dimension ETL with SSIS
- • Slowly Changing Dimension types and theory, Using the SCD wizard, Drilling into the SCD transformations, Customizing the SCD, Alternate SCD approaches, Inferred Members, Handling Snowflake and Parent-Child Hierarchies
- LAB 04-Dim: Dimension Table ETL
Module 04-Fact: Fact Table ETL with SSIS
- Fact types and theory, Aspects of the fact table processing, Dimension lookups with the Lookup transformation, Missing Dimension Members, Measures and Calculations, Handling fact inserts and updates, Changing data grain, Processing Analysis Services Measure Group Partitions
- LAB 04-Fact: Fact Table ETL
Module 04-SSAS: Processing SSAS Objects in SSIS
- Analysis Services processing types, Analysis Services Processing Task and Execute DDL Task, Dynamically creating and processing partitions, ASCMD command line and AMO API usage
- LAB 04-SSAS: Analysis Services Processing
Module 04-Evnt: Notifications, Windows Management Instrumentation, Event Handlers and File Watching
- Leveraging the Send Mail Task, Introduction to WMI, Installing the custom file watcher component
- Lab 04-Evnt: Notifications, WMI, and Event Handlers
Section C: Package Administration and Management
Module 05-Conf: Applying Dynamic Configurations and Built-in Logging
- Planning your configuration environment, SSIS Configuration types, dynamic Property Expressions, SSIS Logging, Extending SSIS auditing with Event Handlers
- Lab 05-Conf: Logging and SSIS Configurations
Module 05-Tran: Understanding SSIS Transaction Management and Restartability
- Built-in checkpoints, How and when to use checkpoints, Managing RDBMS transactions, Rollback considerations, Integrating Checkpoints with Transactions
Module 06: Coordinating Team Development and Deployment, Modular package Design
- Team development environment, Source Control, Deploying Packages, SSIS Deployment Wizard
Module 07: Package Administration: Security and Execution
- Package Execution Options, DTExec command line execution, SSIS Management features, SQL Server storage and security roles, Package encryption
- Lab 07: Creating Modular Packages and Scheduling Packages with SQL Server Agent
Section D: Troubleshooting and Optimization
Module 08: Troubleshooting and Optimizing SSIS Packages
- Identifying package bottlenecks, Understanding the SSIS data flow architecture, Data Flow transformation types, Execution Trees, Engine Threads, Monitoring Data Flow execution with pipeline logging, Debugging Task with Breakpoints, SSIS Performance Monitor counters and tracking
- Lab 08: Setting Breakpoints and Using Performance Monitor
Section E: ETL Case Study and SSIS Frameworks
Module 09: ETL Case Study and Package Frameworks
- Case study examples: package design and performance, Integration with SQL Server 2005 & 2008, using Merge and Change Data Capture features, Partition Table management, Creating an SSIS Framework for Auditing Troubleshooting, and Reporting