Microsoft SQL Server 2005 Integration Services
Course Description
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.
3 days
Contact us for pricing
Prerequisites
Basic knowledge of SQL Server 2005 and some familiarity with SQL Server Management Studio.A Guided Tour of SSIS
Understanding SSISWhat 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 SSISTasks
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
Using Variable and Configurations Between Packages
Parent and Child Scenarios
Direct Configuration
Indirect Configuration
Introduction to Data Flow
Data Flow OverviewData 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 VariablesVariable 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
Deployment Manifest
The Package Installation Wizard
Deploy a Package to the File System
Deploy a Package to SQL Server
Redeploying Updated Packages
Advanced Control Flow
Advanced Control Flow OverviewUsing 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 OverviewCheckpoints
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 TransformationsAsynchronous 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 PackagesDeployment Challenges
Deployment-Sensitive Components
Utilities That Can Help
Create a Package Deployment Utility
Deployment Utility Properties
Deployment Folder
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
Package Management
Overview of Package ManagementManaging 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
Overview of Package ManagementManaging 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
Best Practices
Best Practices for Using SSISPackage Design
Data Flow Techniques
Deployment and Management Best Practices
Best Practices for Package Design
Naming Conventions
Beyond ETL: Other Scenarios for SSIS
Using SSIS Beyond ETLMigrating 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
SQL ServerSQL Server 2005