SUMMARY:   This presentation provides detailed treatment of the following SQL Server 2005 Integration Services (SSIS) topics: Creating and managing SSIS packages Connecting to, querying and transforming disparate data sources Scripting Business Intelligence and data warehousing considerations Event handling SSIS problem determination And the like. AUDIENCE:   Business intelligence and data warehouse developers, ETL architects, DBAs and system administrators. PREREQUISITES:   At least six (6) months experience with SQL Server 7.0 or 2000 DTS. DURATION:   3 days. APPROACH:   Lecture and hands-on (about 70%). OBJECTIVES:   Upon completion of this presentation, the participant should be able to use the SQL Server 2005 toolset to deploy a broad range of SSIS requirements per the ‘Core Topics’ that follow. COURSE CONTENT:   REVIEW OF BI TERMS & 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 & 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? SQL SERVER INTEGRATION SERVICES (SSIS) OVERVIEW Business Intelligence Suite SSIS Architecture Scope Services Run-time engine Data-flow engine Packages Event handlers Logging Variables SSIS Designer Command prompt utilities SSIS TOOLS Business Intelligence Studio SQL Server Management Studio Wizards (e.g., import/export, migration, deployment, etc.) Data Sources Data Source Views PACKAGE CREATION USING SSIS DESIGNER Control flows Data flows Data sources Event handlers Debugging packages Viewing package objects Saving packages TASKS, CONTAINERS & PRECEDENCE CONSTRAINTS Control flow architecture SSIS tasks Containers Sequence container For Loop container Foreach Loop container Precedence constraints Task grouping Annotation Variables Setting task properties via the Properties window Setting task properties via the task editor EXTRACT, TRANSFORM, LOAD (ETL) TERMS & CONCEPTS ETL overview The ETL process ETL staging considerations Source-to-destination mapping considerations Source system monitoring document Normalised (OLTP) vs. dimensional model Planning source-to-ETL processing How to handle ‘inferred members’ (late arriving dimensions) Fact table properties & overview Slowly Changing Dimensions (SCD) Surrogate keys DATA FLOWS Data flow tasks Data flow components Connection managers Data sources Destinations SSIS transformations SSIS TRANSFORMATION EXAMPLES Aggregation Character Map Conditional Split Data Conversion Derived Column Fuzzy Lookup/Fuzzy Grouping Merge and Merge Join Multicast Sort Script Component Slowly Changing Dimension ERROR & EVENT HANDLING Types Audit Precedence constraints Script debugging Logging SSIS BUSINESS SCENARIOS – WORKSHOPS ETL requirements Data source and destination SSIS project Connection managers Data flows Lookup transformations Foreach Loop container Flat file connection manager Package configurations Directory property configurations Logging Error and exception handling SSIS SCRIPTING SSIS scripting options Script task vs. Script component Expressions and scripting ActiveX Script task Script task vs. Custom managed tasks Visual Studio for Applications (VSA) Debugging scripts DEPLOYMENT Deployment facilities Deployment controls How to organise a package for deployment How to organise configuration files and variables How to create destination computer bundle How to install the deployment bundle on the destination computer Package execution options Package security and roles considerations DTS TO SSIS MIGRATION ISSUES Why migrate? Some DTS and SSIS differences DTS Data Pump concepts SSIS Control Flow/Data Flow concepts Summary of important migration issues Migrating DTS Packages Migration tools Migration issues Task migration Migrating precedence constraints and variables Migrating connections Migrating passwords and scripts PERFORMANCE CONSIDERATIONS & BEST PRACTICES Naming conventions Queries Controlled sorts Optimised aggregation Data loads GOW/08