This course will enable technology professionals with little or no ETL experience and minimal exposure to SQL Server 2005 and Visual Studio 2005 to be comfortable and productive with the SSIS tools and technologies. In this course you will learn about the SQL Server Management Studio (SSMS) and building Database Maintenance Plans. You will be introduced to the Business Intelligence Development Studio (BIDS) and working with Control Flow and Data Flow. You will also become familiar with SSIS package management and package deployment along with learning to write solid code using debugging, error handling and logging techniques.
SQL Training Audience:
SQL Server Users.
SQL Training Prerequisites
Basic knowledge of SQL Server 2005 and some familiarity with SQL Server Management Studio
SQL Training Course duration
3 days
SQL Training Course outline
A Guided Tour of SSIS
- Understanding SSIS
- What is ETL?
- Parts of an SSIS Package
- Tools for Building SSIS Packages
- SSIS Package Storage Options
- Other Ways to Launch the Import and Export Wizard
- Exploring and Executing an SSIS Package in BIDS
- The BIDS Interface
- SSIS BIDS Components
- The Control Flow Designer
- Data Flow Designer
- Connection Managers
- Event Handler Designer
- Package Explorer
- Progress Pane
- Executing a Package in BIDS
- Exploring and Executing a Package Outside of BIDS
Introduction to Control Flow
- Overview of Control Flow in SSIS
- Tasks
- Containers
- Precedence Constraints
- Control Flow Tasks
- Workflow Tasks
- The Execute SQL Task
- File System Task
- FTP Task
- Send Mail Task
- Other SQL Server Operations Tasks
- Precedence Constraints
- Why Use Precedence Constraints?
- Implementing Precedence Constraints
- Data Processing Tasks
- Considerations
Introduction to Data Flow
- Data Flow Overview
- Data Flow Pipeline
- Data Flow Sources
- Data Flow Destination
- Data Viewers
- Data Flow Transformations
- Row Transformations
- Rowset Transformations
- Split and Join Transformations
- Business Intelligence Transformations
- Other Transformations
Variables and Configurations
- Understanding Variables
- Variable Properties
- Where Can You Use Variables?
- Variable Scope
- Scope and the Execute Package Task
- Using Variables in Control Flow
- Execute SQL Task
- File System Task
- Send Mail Task
- Foreach Loop Container
- Script Task
- Precedence Constraint Expressions
- Property Expressions
- Using Variables in Data Flow
- Connection Managers
- Derived Column Transformation
- Conditional Split Transformation
- Parameters
- Understanding Property Expressions
- Using Property Expressions
- Understanding Configurations
- Using Configurations
- Configuration Storage
- Using Variable and Configurations Between Packages
- Parent and Child Scenarios
- Direct Configuration
- Indirect Configuration
Advanced Control Flow
- Advanced Control Flow Overview
- Using Containers
- Container Properties
- Task Host Container
- Sequence Container
- For Loop Container
- Foreach Loop Container
- Grouping Container
- Transaction Support in SSIS
- Additional Control Flow Tasks
- Execute DTS 2000 Package Task
- Execute Process Task
- FTP Task
- Web Service Task
Error Handling and Logging
- Error Logging and Handling Overview
- Checkpoints
- Package Logging
- Configuring Package Logging
- Logging to a Text File
- Logging in Windows Event Log
- Custom Logging
- Error Handling in Control Flows
- Event Handling
- Error Outputs in Data Flows
Advanced Data Flow
- Synchronous Transformations
- Asynchronous Transformations
- Working with Error Outputs
- Error Actions
- Using Advanced Transformations
- Audit
- Multicast
- Conditional Split
- Derived Column Transformation
- Union All Transformation
- Merge Transformation
- Lookup Transformation
- Fuzzy Lookup Transformation
Package Deployment
- Overview of Deploying Packages
- Deployment Challenges
- Deployment-Sensitive Components
- Utilities That Can Help
- Create a Package Deployment Utility
- Deployment Utility Properties
- Deployment Folder
- Deployment Manifest
- The Package Installation Wizard
- Deploy a Package to the File System
- Deploy a Package to SQL Server
- Redeploying Updated Packages
Package Management
- Overview of Package Management
- Managing Package with DTUtil
- Managing Packages with SQL Server Management Studio
- Connecting to Integration Services
- Executing Packages
- Execute Package Utility
- Executing Packages at the Command Line
- Scheduling Packages with SQL Server Agent
- SSIS Security
- Protecting Sensitive Package Information
- Package Storage
- SQL Server Package Security
- Authorizing SSIS Users in SQL Server
- Signing Packages with Digital Certificates
Introduction to Scripting and Custom Components
- Extending SSIS Capabilities Through Code
- Overview of SSIS Scripting
- Overview of Custom Component Development
- SSIS Scripting
- What Can SSIS Scripting Do?
- When Should I Use SSIS Scripting?
- When Should I Not Use SSIS Scripting?
- Script Editor
- SSIS Object Model
- Scripting in Control Flows
- Script Task
- Variables
- Going Beyond Built-In Tasks
- Scripting in Data Flows
- Script Component
- Input and Output Columns
- Script Component as Source
- Script Component as Destination
- Script Component as Transformations
- Going Beyond Built-In Data Flow Components
- Script Compilation
- Custom SSIS Components
- Overview of Component Development
- Third-Party SSIS Components
Best Practices
- Best Practices for Using SSIS
- Package Design
- Data Flow Techniques
- Deployment and Management Best Practices
- Best Practices for Package Design
- Naming Conventions
- Development Standards
- Source Control
- Modular Design
- Looping Operations
- Error Logging
- Precedence Constraints
- Try/Catch Blocks
- Auditing Information
- Package Templates
- Data Flow Best Practices
- Data Flow Source Performance, Maintenance, and Ease of Use
- Data Flow Destination
- Other Performance Best Practices
- Security Best Practices
- Management Best Practices
Beyond ETL: Other Scenarios for SSIS
- Using SSIS Beyond ETL
- Migrating SQL Servers with SSIS
- 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
- Folder and File Management
- Working with the Windows Event Log
- The WMI Data Reader Task
- The WMI Event Watcher Task
