SQL Server Integration Services Training

Microsoft SQL Server Integration Services

• Introduction
  • Prerequisites
  • Installing the Practice Files
    • Software Requirements
    • Security
    • A Word about the Sample Files
    • Script Language
    • Sample Databases
    • A Note about Excel
    • Installation
  • About the Author
• A Guided Tour of Integration Services
  • Understanding Integration Services
    • What is ETL?
    • Integration Services Packages
    • Tools for Building Integration Services Packages
    • Integration Services Package Storage Options
    • Creating a Package with the Import and Export Wizard
    • Other Ways to Launch the Import and Export Wizard
  • Exploring and Executing an Integration Services Package in BIDS
    • The BIDS Interface and Components
    • Executing a Package in BIDS
  • Exploring and Executing a Package Outside of BIDS
• Lab 1: A Guided Tour of Integration Services
  • Lab 1 Overview
  • Use the Import and Export Wizard
  • Use BIDS to Create a Package Manually
  • Execute a Package with BIDS and DTExecUI.exe
• Control Flow
  • Overview of Control Flow in Integration Services
    • Elements in a Control Flow
    • Control Flow Tasks
  • Working with Workflow Tasks
    • The Execute SQL Task
    • File System Task
    • FTP Task
    • Send Mail Task
  • Precedence Constraints
    • Why Use Precedence Constraints?
    • Implementing Precedence Constraints
    • Recommendations
• Lab 2: Control Flow
  • Lab 2 Overview
  • Work with the Execute SQL Control Flow Task
  • Implement Precedence Constraints
• Data Flows
  • The Data Flow Task
    • Data Flow Pipeline
    • Data Flow Sources
    • Data Flow Destinations
  • Data Viewers
  • Data Flow Transformations
    • Row Transformations
    • Rowset Transformations
    • Split and Join Transformations
    • Business Intelligence Transformations
    • Other Transformations
• Lab 3: Data Flows
  • Lab 3 Overview
  • Create a Data Flow Source
  • Create a Data Flow Destination and Data Viewer
  • Create a Package Using Several Transformations
• Variables and Configurations
  • Understanding Variables
    • Variable Properties
    • Variable Data Types
    • Variable Scope
    • Where Can You Use Variables?
    • Scope and the Execute Package Task
  • Using Variables in Control Flows
    • Execute SQL Task
    • File System Task
    • Precedence Constraint Expressions
    • Property Expressions
    • Send Mail Task
    • Foreach Loop Container
    • Script Task
  • Using Variables in Data Flow
    • Connection Managers
    • Derived Column Transformation
    • Conditional Split Transformation
    • Parameters
  • Configurations
    • Using Configurations
    • Configuration Storage
  • Using Variables and Configurations Between Packages
    • Parent and Child Scenarios
    • Direct Configuration
    • Indirect Configuration
• Lab 4: Variables and Configurations
  • Lab 4 Overview
  • Using Variables with Parameterized Queries
  • Working with Property Expressions
  • Working with XML Configuration Files
• Advanced Control Flow
  • Advanced Control Flow Overview
  • Using Containers
    • Container Properties
    • Task Host Container
    • Sequence Container
    • Foreach Loop Container
    • For Loop Container
    • Grouping Container
  • Transaction Support in Integration Services
• Lab 5: Advanced Control Flow
  • Lab 5 Overview
  • Import Data form CSV Files with a Foreach Loop Container
• Error Handling and Logging
  • When Things Go Wrong
  • Checkpoints
  • Handling Errors and Debugging
    • Error Handling in Control Flows with Precedence Constraints
    • Error Outputs in Data Flows
    • Breakpoints
  • Package Logging
    • Configuring Package Logging
    • Logging to a Text File
    • Logging to the Windows Event Log
    • Custom Logging
  • Event Handling
• Lab 6: Error Handling and Logging
  • Lab 6 Overview
  • Create a Package that Fails
  • Redirect Problem Rows Using Error Outputs
• Advanced Data Flow
  • Synchronous and Asynchronous Transformations
    • Synchronous Transformations
    • Asynchronous Transformations
  • Using Advanced Transformations
    • Audit Transformation
    • Multicast Transformation
    • Derived Column Transformation
    • Union All Transformation
    • Merge Transformation
    • Lookup Transformation
    • Fuzzy Lookup Transformation
    • Conditional Split Transformation
  • Handling Slowly Changing Dimensions
    • Loading a Data Warehouse
    • The Slowly Changing Dimension Transformation
• Lab 7: Advanced Data Flow
  • Lab 7 Overview
  • Work with the Lookup Transformation
  • Work with Derived Column and Conditional Split Transformations
• Package Deployment
  • Deploying Packages
    • Deployment Challenges
  • Create a Package Deployment Utility
    • Deployment Utility Properties
    • Deployment Folder
    • Deployment Manifest
  • Installing a Package
    • The Package Installation Wizard
    • Deploy a Package to the File System
    • Deploy a Package to the SQL Server
    • Importing a Package Using Management Studio
    • Should You Deploy to SQL Server or the File System?
  • Redeploying Updated Packages
• Lab 8: Package Deployment
  • Lab 8 Overview
  • Create a Package Deployment Utility
  • Deploy a Package to the File System
  • Deploy a Package to the SQL Server
• Package Management
  • Overview of Package Management
  • Managing Integration Services Packages
    • Managing Packages with DTUtil
    • Managing Packages with Management Studio
  • Executing Packages
    • DTExecUI: The Execute Package Utility
    • Executing Packages at the Command Line with DTExec
    • Scheduling Package Execution wit SQL Server Agent
  • Integration Services Security
    • Protecting Sensitive Package Information
    • Controlling Access to Packages Stored in SQL Server
    • Signing Packages with Digital Certificates
• Lab 9: Package Management
  • Lab 9 Overview
  • Store a Package in SQL Server
  • Use DTUtil to Move a Package
  • Schedule a Package with SQL Agent
• Scripting and Custom Components
  • Extending Integration Services Capabilities through Code
    • Integration Services Scripting
    • Custom Component Development
    • Integration Services Scripting
    • Integration Services Object Model
    • Visual Studio Tools for Applications Script Editor
  • Scripting in Control Flows with the Script Task
    • Variables
    • Going Beyond Built-In Tasks
  • Scripting in Data Flows with the Script Component
    • Input and Output Columns
    • Script Component Types
    • Synchronous and Asynchronous Script Transformations
    • Going Beyond Built-In Data Flow Components
  • Custom Integration Services Components
    • Custom Component Development
    • Third-Party Integration Services Components
• Lab 10: Scripting and Custom Components
  • Lab 10 Overview
  • Create a Custom Asynchronous Transformation
  • Create a Custom Performance Logging Task
• Best Practices
  • Best Practices for Using Integration Services
    • Keep It Simple
  • Best Practices for Package Development and Design
    • Development Standards
    • Package Design Best Practice
    • Error Handling and Logging
  • Data Flow Best Practices
    • Data Flow Source Performance
    • Data Flow Transformation Performance, Maintenance, and Ease of Use
    • Data Flow Destinations
    • Integration Services Engine Best Practices
  • Deployment and management Best Practices
    • Deployment Best Practices
    • Security Best Practices
    • Management Best Practices
• Lab 11: Best Practices
  • Lab 11 Overview
  • Compare OLE DB Destination Settings
  • Use SQL Server Profiler for Performance Tuning
  • Create and Use a Template
• Going Beyond ETL
  • Using Integration Services Beyond ETL
  • Migrating and Maintaining SQL Servers with Integration Services
    • SQL Server Transfer Tasks
    • SQL Server Maintenance Plans
  • Working with Analysis Services
    • The Analysis Services Processing Task
    • Dimension and Partition Processing Destinations
    • Analysis Services Execute DDL Task
    • Data Mining Query Task
    • Slowly Changing Dimension Transformation
  • Working with Windows Management Instrumentation
    • The WMI Data Reader Task
    • The WMI Event Watcher Task
• Lab 12: Going Beyond ETL
  • Lab 12 Overview
  • Transfer SQL Server Objects
  • Rebuild an Index with the Rebuild Index Task

Before Taking this Class

This course assumes no prior knowledge of SQL Server Integration Services. This course does assume prior knowledge of SQL Server and the use of SQL Server Management Studio for development and administrative tasks. You should be able to create CRUD (create, retrieve, update, and delete) queries using T-SQL, understand basic relational databases design, run script files and diagnose problems that occur, and have experience building applications that access data stored in SQL Server. You must also know how to connect to an instance of SQL Server using the various connection dialog boxes in Management Studio and development tools.

Software/Setup For this Class

SQL Server 2012 or higher.