Home About Courses Schedule Services Webinars Contact Search

SQL Server Integration Services

SEE SCHEDULE

Duration: 3.0 days

Method: Instructor led, Hands-on workshops

Price: $1800.00

Course Code: SS2201


Audience

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, DBAs 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.

Description

This course will enable technology professionals with little or no ETL experience to be comfortable and productive with the SSIS tools and technologies. In this course you will learn about the Visual Studio Development Tools (VSDT) formerly known as BIDS) and work with Control and Data Flows to build workflows to extract, transform, and load data using a variety of data sources, transformations, and destinations. You will also become familiar with SSIS package management and package deployment along with learning to employ robust debugging, error handling, and logging techniques.

Objectives

Upon successful completion of this course, the student will be able to:

  • How to use VSDT to create well-designed packages
  • About Control Flows, how to build them using many of the built-in tasks, including the various containers
  • How to use the Data Flow task to perform primary ETL operations
  • About variable and configurations to make your packages dynamic and resilient to changes in environments
  • About the features in VSDT and Integration Services that help you troubleshoot a package
  • How to deploy and manage packages effectively
  • How to write scripts so that you can go beyond the built-in tasks and explore the possibilities of creating custom tasks, data sources, destinations, and transformations
  • Best practices that will make it easier to develop and maintain a package over its entire life cycle
  • About using Integration Services for tasks other than traditional ETL operations

Prerequisites

This course assumes no prior knowledge of SQL Server Integration Services. This course does assume prior knowledge of SQL Server. Knowledge in working with SQL Server Management Studio for development and administrative tasks would be helpful. You should be able to create CRUD (create, retrieve, update, and delete) queries using T-SQL, understand basic relational database design, run script files and diagnose problems that occur, and have experience building applications that access data stored in SQL Server. Other tools used in this course include Notepad, Microsoft Excel, and basic features of Windows.

Topics

  • I. A Guided Tour of Integration Services
    • Understanding Integration Services
    • Exploring and Executing an Integration Services Package in Visual Studio Development Tools (VSDT)
    • Exploring and Executing a Package Outside of VSDT
  • II. Control Flow
    • Overview of Control Flow in Integration Services
    • Working with Workflow Tasks
    • Precedence Constraints
  • III. Data Flows
    • The Data Flow Task
    • Data Viewers
    • Data Flow Transformations
  • IV. Variables and Configurations
    • Understanding Variables
    • Using Variables in Control Flow
    • Using Variables in Data Flow
    • Configurations
    • Using Variables and Configurations between Packages
  • V. Advanced Control Flow
    • Advanced Control Flow Overview
    • Using Containers
    • Transactions Support in Integration Services
  • VI. Error Handling and Logging
    • When Things Go Wrong
    • Checkpoints
    • Handling Errors and Debugging
    • Packaging Logging
    • Event Handling
  • VII. Advanced Data Flow
    • Synchronous and Asynchronous Transformations
    • Using Advanced Transformations
    • Handling Slowly Changing Dimensions
  • VIII. Package Deployment
    • Deploying Packages
    • Create a Package Deployment Utility
    • Installing a Package
    • Redeploying Updated Packages
  • IX. Package Management
    • Overview of Package Management
    • Managing Integration Services Packages
    • Executing Packages
    • Integration Services Security
  • X. Scripting and Custom Components
    • Extending Integration Services Capabilities through Code
    • Scripting in Control Flows with the Script Task
    • Scripting in Data Flows with the Script Component
    • Custom Integration Services Components
  • XI. Best Practices
    • Best Practices for Using Integration Services
    • Best Practices for Package Development and Design
    • Data Flow Best Practices
    • Deployment and Management Best Practices
  • XII. Going Beyond ETL
    • Using Integration Services Beyond ETL
    • Migrating and Maintaining SQL Servers with Integration Services
    • Working with Analysis Services
    • Working with Windows Management Instrumentation