Working with SQL Server Integration Services 

2-Day (COURSE-BI-SSIS) Overview For most Business Intelligence projects, it is estimated that over 80% of the total work in a project is related to the extraction, transformation, and loading (ETL) of data. This course focusses on the core concepts of how to use SQL Server Integration Services (SSIS) to achieve this outcome. The course is divided into two parts. Day 1 covers the core concepts involved with using SSIS. While SSIS can be used to automate a wide variety of tasks, loading dimensional data models from source systems is the most common use case for SSIS. In Day 2 we present the loading of a dimensional data model as a specific case study for using SSIS. Duriing Day 2, additional SSIS techniques (including some more advanced techniques) are presented. For details on booking courses or seminars, please visit our booking page.  Audience This course is intended for IT Professionals that are responsible for designing, developing, deploying, and operating business intelligence solutions. While this course is based on SQL Server 2008R2, detailed coverage is provided on earlier versions ie: SQL Server 2005, SQL Server 2008. Differences between versions are highlighted. Prerequisites The target audience for this session is database warehouse, decision support, and business intelligence solution architects, IT professionals, and business intelligence developers who want to learn the details of how to use the capabilities provided by SSIS to build ETL or other data integration solutions. While this course is targeted at database professionals with some experience with data warehouse solutions, this workshop can be completed by attendees without prior experience of SQL Server 2000 DTS or SQL Server 2005/2008 SSIS. Course Objectives This course objectives are: Learning SSIS Features and Functionality: The initial day dives into all the core feature set of SSIS to lay the foundation for leveraging the tool for data processing requirements. The day finishes with a module that describes deployment and ongoing administration needs for SSIS. Understanding and Applying SSIS Funcationality: Building on the SSIS knowledge that was obtained in the first day, the focus of day two is to apply the knowledge of how to use SSIS. While SSIS can be used for a wide variety of tasks, Day 2 presents a specific case study on using SSIS to load a dimensional data model and uses some more advanced SSIS techniques as they are needed. Deploying and Administering SSIS in Production: The course covers getting ETL solutions over the goal line via deployment and ongoing administration. Course Outline Day 1 Course Introduction Module IS201 Overview of SQL Server Integration Services Introduction to the Microsoft BI platform  Integration Services features overview Working with SQL Server BI tooling Creating SSIS Projects and Packages LAB: Creating SSIS Projects and Packages Module IS202 - Working with SSIS Control Flow Introduction to Control Flow tasks and precedence constraints Working with containers Passing information between tasks using variables and expressions  LAB: Working with SSIS Control Flow Module IS203 - Working with SSIS Data Flow Introduction to Data Flow Data Sources, Destinations and Assistants Transforming data Debugging using data viewers and breakpoints LAB: Working with SSIS Data Flow Module IS204 - SSIS Configuration, Deployment, Scheduling and Logging Project and package configurations Deploying projects or packages Scheduling package execution Logging package activity LAB: SSIS Configuration, Deployment, Scheduling and Logging Day 2 Module IS301 - SSIS Case Study: Dimensional Modelling Core Concepts Why implement dimensional models? Dimension table core concepts Fact table core concepts Profiling incoming data LAB: Dimensional Modelling Core Concepts Module IS302 - Loading Dimension Tables Issues with loading dimension tables  Implementing slowly changing dimensions Implementing inferred members Useful additional transformations LAB:  Loading Dimension Tables Module IS303 - Loading Fact Tables Issues with loading fact tables Working with granularity and calculations  Useful additional transformations LAB: Loading Fact Tables Module IS304 - Designing for Restartability and Performance Designing for restartability Improving SSIS package performance SSIS tasks vs T-SQL tasks LAB: Designing for Restartability and Performance