cool hit counter

PASS Community Summit 2008

Forgot Password

Course Details

Course Title:	End to End Integration Services Register Now!
Duration:	4 days
Overview:	A four day class designed to help you understand all aspects of SQL Server 2005 Integration Services (SSIS), the ETL tool of choice for SQL Server DBAs. We will use a variety of lab exercises and small projects to ensure you gain familiarity in how the design and execution environments work and be prepared to build your own real world applications using this SQL Server subsystem. Students will learn how to use SSIS in the real-world. With the hands-on labs, you will learn how to create a SSIS package to load an OLTP or warehouse environment. You?ll also learn how to make the package dynamic to where it will work and self-heal in the event of a failure. This course gives detailed and extensive demos on how to use SSIS in your enterprise environment and how to extend SSIS with scripting.
Prerequisites:	The target of this class is at developers, architects or IT professionals who have basic SQL skills already. This class assumes that you have no previous knowledge of DTS or SSIS. In some of the scripting chapters, it is useful (but not mandatory) that the student has some basic VB experience. You will need to know how to run a query inside of SQL Server 2005.
Course Materials:	All students receive a copy of Expert SQL Server 2005 Integration Services plus a printed copy of the the class presentation as part of the registration fee
Outline:	Day 1 - SSIS Features In-depth Introduction to the SSIS environment Give a demonstration of Business Intelligence Development Studio (BIDS) How to create a shared connection. Tasks Overview of the commonly used tasks. Hands-on Lab: Create your first package. Drilling into the Data Flow Overview of the commonly used transforms, source and destinations. Hands-on Lab: Creating your first data flow. Expressions and Variables The expression language in SSIS. The scoping of variables. Debugging SSIS with the watch window How to use variables and expressions to make a package dynamic. Hands-on Lab: Creating a dynamic package using variables. Containers Using the various containers. Looping through tasks. Hands-on Lab: Looping through a series of tasks. Day 2 - Small Group Project - Creating an End-to-End Package ? Design and architect an end-to-end SSIS package to loop through a directory and load data into a table. The Script Task and Transform Hands-on Lab: Cleansing Data with the Script Transform Advanced Tasks Web Service task XML Task WMI tasks Hands-On Lab: Web Service and XML tasks Advanced Transforms Fuzzy Grouping transform Fuzzy Lookup transform Term Extraction transform Term Lookup transform Raw Files Import and Export Column transforms Hands-On Lab: Term Extractions, Term Lookups, Export Column and More Event Handling and Logging in SSIS Event Handlers Logging Providers Hands-On Lab: Creating a basic event handler Day 3 - Basic Data Warehousing Loading the Data Warehouse Loading a fact table Loading a dimension with the SCD Wizard Hands-On Lab: Loading a Dimension Loading a fact table Tactics for quickly loading a fact table Hands-on Lab: Loading a fact table Migration from DTS Process of migrating a package to SQL Server 2005 SSIS Issues that you will encounter Hands-on Lab: Migrating a series of packages. Administrating SSIS The command-line utilities Deploying a package. Scheduling SSIS packages. Security in SSIS Hands-on lab: deploying and scheduling a series of packages. Enterprise package development. Restartability of packages Package configurations Transactions in a package Advanced settings in SSIS Hands-on lab: Creating a package with checkpoints Day 4 - Group Project Our goal on the 4^th day is to give students enough hands-on experience to where they can go back to work the next week on rather complex ETL processes in their real-world environment. The signature of these classes is the 4^th day, where you take on large SSIS real-world problems in small groups of 2 to 3 with the instructor?s assistance when needed. Medium-scoped group project 1 (DTS Upgrade): Upgrading a series of packages from SQL Server 2000 DTS to SSIS. In this lab, the student takes a few packages out of DTS and migrate them to SSIS, fixing issues that may arrive. Then, the students will deploy them and schedule them to in SSIS to ensure no loss of data. Large-scoped group project 2 (Data Warehouse): Take a series of data sources from proprietary systems from several counties throughout the US, and merge them into a single data warehouse. Data will need to be cleansed and data types won?t be correct. Large-scoped group project 3 (OLTP Load): Create an intelligent package that can detect when a file is placed into a directory and load it into an SQL Server.