SQL Server // BI6235

Implementing and Maintaining Microsoft SQL Server 2008 Integration Services

Explore SQL Server Integration Services (SSIS) in SQL Server 2008

Course Description

During this 4-day course, you will learn the ins and outs of SSIS and how to apply its enterprise functionality to data warehouse ETL systems and general data integration requirements. At the completion this course, you will understand how to design, develop, deploy, and operate SSIS solutions from source systems extractions, to data integration and transformation, to business intelligence system loading including how to processing SQL Server Analysis Services (SSAS) cubes. This class incorporates a team-based case study. The case study provides the students with a real world scenario via a set of requirements. The team must design and implement their solution based on the requirements. This gives the students the ability to exercise the skills they learned during the class in a real world situation.

Audience

This course is intended for information technology (IT) professionals and developers who need to implement data transfer or extract, transform, and load (ETL) solutions by using Microsoft SQL Server 2008 Integration Services. Exams: 70-448: Microsoft SQL Server 2008, Business Intelligence Development and Maintenance

Prerequisites

Before attending this course, students must have:

  • Exposure to enterprise data import and export scenarios.
  • Experience navigating the Microsoft Windows Server environment.
  • Exposure to Visual Studio.
  • Experience with Microsoft SQL Server, including:
  • SQL Server Agent.
  • SQL Server query language (SELECT, UPDATE, INSERT, and DELETE).
  • SQL Server System tables.
  • SQL Server accounts (users and permissions).
In addition, it is recommended, but not required that students have completed: 
SQL200: Introduction to Transact-SQL

What You Will Learn

After attending this course, students will be able to:

  • Introduction to SQL Server 2008 Integration Services
  • Developing Integration Services Solutions
  • Implementing Control Flow
  • Implementing Data Flows
  • Debugging and Error Handling
  • Implementing Checkpoints and Transactions
  • Deploying Packages
  • Managing and Securing Packages

Course Outline

1. Introduction to SQL Server 2008 Integration Services
  • Overview of Integration Services Solutions
  • Extracting, transforming, and loading data
  • Integration Services Tools

    Lab 1: Using SQL Server Integration Services
    Exercise 1: Using the Import and Export Wizard
    Exercise 2: Running an Integration Services Package

2. Developing Integration Services Solutions
  • Creating an Integration Services Solution
  • Using Variables
  • Building and Running a Solution

    Lab 2: Implementing an Integration Services Solution
    Exercise 1: Creating an Integration Services Project
    Exercise 2: Implementing a Package
    Exercise 3: Building and Running an Integration Services Project

3. Implementing Control Flow
  • Control Flow Tasks
  • Control Flow Precedence Constraints
  • Control Flow Containers

    Lab 3: Implementing Control Flow
    Exercise 1: Creating a Simple Control Flow
    Exercise 2: Configuring Precedence Constraints
    Exercise 3: Using Containers

4. Implementing Data Flow
  • Data Flow Sources and Destinations
  • Basic Data Flow Transformations
  • Advanced Data Flow Transformations
  • Data Flow Paths

    Lab 4: Implementing Data Flows
    Exercise 1: Transferring Data
    Exercise 2: Implementing Transformations
    Exercise 3: Using Data Viewers
    Exercise 4: Configuring Error Output

5. Implementing Logging

  • Overview of Integration Services Logging
  • Implementing Logging

    Lab 5: Implementing Logging
    Exercise 1: Configuring Logging
    Exercise 2: Implementing Custom Logging

6. Debugging and Error Handling

  • Debugging a Package
  • Implementing Error Handling

    Lab 6: Debugging and Error Handling
    Exercise 1: Debugging a Package
    Exercise 2: Implementing Error Handling
    Exercise 3: Controlling Failure Behavior

7. Implementing Checkpoints and Transactions

  • Implementing Checkpoints
  • Implementing Transactions

    Lab 7: Implementing Checkpoints and Transactions
    Exercise 1: Implementing Checkpoints in a Package
    Exercise 2: Implementing Transactions in a Package
    Exercise 3: Implementing a Native Transaction

8. Deploying Packages
  • Package Configurations
  • Deploying Packages

    Lab 8: Deploying Integration Services Packages
    Exercise 1: Creating a Package Configuration
    Exercise 2: Preparing a Package for Deployment
    Exercise 3: Deploying a Package

9.  Managing and Securing Packages
  • Managing Packages
  • Securing Packages

    Lab 9: Managing and Securing Packages
    Exercise 1: Importing a Package
    Exercise 2: Configuring and Executing a Package
    Exercise 3: Scheduling a Package
    Exercise 4: Securing a Package

10. Integration Services Case Study
  • Design and create packages based on user requirements.
  • Deploy the packages into a production environment.
  • Secure the packages after deployment​