SSIS Training: Introduction to SQL Server Integration Services
Duration: 3 days
SSIS Training Prerequisites
All SSIS training attendees should have prior experience working with SQL Server . Hands-on/Lecture Ratio
This SSIS training is 70% hands-on, 30% lecture, with the longest lecture segments lasting for 10 minutes.
SSIS Training Materials
All SSIS training students receive a copy of Wrox Press’s Professional SQL Server Integration Services.
SSIS Training Objectives All attendees will learn to:
• Use the SSIS tools, tasks, and containers
• Work with variables, parameters, and expressions
• Create end-to-end packages
• Script in SSIS
• Populate a data warehouse using SSIS
• Program and extend SSIS
• Understand and tune the data flow engine
• Add a user interface
• Handle errors
• Perform External Management and WMITask Implementations
SSIS Training Outline
• Welcome To SQL Server Integration Services
o SQL Server SSIS Historical Overview
o What's New in SSIS
o Tools of the Trade
o Import and Export Wizard
o The SQL Server Data Tools Experience
o SSIS Architecture
o Packages
o Control Flow
o Data Flow
o Variables
o Parameters
o Error Handling and Logging
o Editions of SQL Server
• The SSIS Tools
o Import and Export Wizard
o SQL Server Data Tools
o Creating Your First Package
o The Solution Explorer Window
o The Toolbox
o The Properties Windows
o The SSIS Package Designer
o Control Flow
o Connection Managers
o Variables
o Data Flow
o Parameters
o Event Handlers
o Package Explorer
o Executing a Package
o Management Studio
• SSIS Tasks
o SSIS Task Objects
o Using the Task Editor
o The Task Editor Expressions Tab
o SSDTCommon Properties
o Looping and Sequence Tasks
o Script Task (.NET)
o Analysis Services Tasks
o Analysis Services Execute DDL Task
o Analysis Services Processing Task
o Data Mining Query Task
o Data Flow Task
o Data Preparation Tasks
o Data Profiler
o File System Task
o Archiving a File
o FTP Task
o Getting a File Using FTP
o Web Service Task
o Retrieving Data Using the Web Service Task and XML Source Component
o XML Task
o Validating an XML File
o RDBMS Server Tasks
o Bulk Insert Task
o Using the Bulk Insert Task
o Execute SQL Task
o Workflow Tasks
o Execute Package Task
o Execute Process Task
o Message Queue Task
o Send Mail Task
o WMI Data Reader Task
o WMI Event Watcher Task
o Polling a Directory for the Delivery of a File
o SMO Administration Tasks
o Transfer Database Task
o Transfer Error Messages Task
o Transfer Logins Task
o Transfer Master Stored Procedures Task
o Transfer Jobs Task
o Transfer SQL Server Objects Task
• Containers
o Task Host Containers
o Sequence Containers
o Groups
o For Loop Container
o Foreach Loop Container
o Foreach File Enumerator Example
o Foreach ADO Enumerator Example
• The Data Flow
o Understanding the Data Flow
o Data Viewers
o Sources
o OLE DB Source
o Excel Source
o Flat File Source
o Raw File Source
o XML Source
o ADO.NET Source
o Destinations
o Excel Destination
o Flat File Destination
o OLE DB Destination
o Raw File Destination
o Recordset Destination
o SQL Server and Mobile Destinations
o Data Mining Model Training
o DataReader Destination
o Dimension and Partition Processing
o Common Transformations
o Synchronous versus Asynchronous Transformations
o Aggregate
o Conditional Split
o Data Conversion
o Derived Column
o Lookup
o Row Count
o Script Component
o Slowly Changing Dimension
o Sort
o Union All
o Other Transformations
o Audit
o Character Map
o Copy Column
o Data Mining Query
o DQS Cleansing
o Export Column
o Fuzzy Lookup
o Fuzzy Grouping
o Import Column
o Merge
o Merge Join
o Multicast
o OLE DB Command
o Percentage and Row Sampling
o Pivot Transform
o Unpivot
o Term Extraction
o Term Lookup
• Using Variables, Parameters, And Expressions
o Dynamic Package Objects
o Variable Overview
o Parameter Overview
o Expression Overview
o Understanding Data Types
o SSIS Data Types
o Date and Time Type Support
o How Wrong Data Types and Sizes Can Affect Performance
o Unicode and Non-Unicode Conversion Issues
o Casting in SSIS Expressions
o Using Variables and Parameters
o Defining Variables
o Defining Parameters
o Variable and Parameter Data Types
o Working with Expressions
o C#-Like? Close, but Not Completely
o The Expression Builder
o Syntax Basics
o Using Expressions in SSIS Packages
• Joining Data
o The Lookup Transformation
o Using the Merge Join Transformation
o Contrasting SSIS and the Relational Join
o Lookup Features
o Building the Basic Package
o Using a Relational Join in the Source
o Using the Merge Join Transformation
o Using the Lookup Transformation
o Full-Cache Mode
o No-Cache Mode
o Partial-Cache Mode
o Multiple Outputs
o Expressionable Properties
o Cascaded Lookup Operations
o Cache Connection Manager and Cache Transform
• Creating An End-To-End Package
o Basic Transformation Tutorial
o Creating Connections
o Creating the Control Flow
o Creating the Data Flow
o Completing the Package
o Saving the Package
o Executing the Package
o Typical Mainframe ETL with Data Scrubbing
o Creating the Data Flow
o Handling Dirty Data
o Finalizing
o Handling More Bad Data
o Looping and the Dynamic Tasks
o Looping
o Making the Package Dynamic
• Scripting in SSIS
o Introducing SSIS Scripting
o Getting Started in SSIS Scripting
o Selecting the Scripting Language
o Using the VSTA Scripting IDE
o Example: Hello World
o Adding Code and Classes
o Using Managed Assemblies
o Example: Using Custom .NET Assemblies
o Using the Script Task
o Configuring the Script Task Editor
o The Script Task Dts Object
o Accessing Variables in the Script Task
o Connecting to Data Sources in a Script Task
o Raising an Event in a Script Task
o Writing a Log Entry in a Script Task
o Using the Script Component
o Diff from a Script Task
o Configuring the Script Component Editor
o Accessing Variables in a Script Component
o Connecting to Data Sources in a Script Component
o Raising Events
o Logging
o Example: Data Validation
o Synchronous versus Asynchronous
o Essential Coding, Debugging, and Troubleshooting Techniques
o Structured Exception Handling
o Script Debugging and Troubleshooting
• Loading A Data Warehouse
o Data Profiling
o Initial Execution of the Data Profiling Task
o Reviewing the Results of the Data Profiling Task
o Turning Data Profile Results into Actionable ETL Steps
o Data Extraction and Cleansing
o Dimension Table Loading
o Loading a Simple Dimension Table
o Loading a Complex Dimension Table
o Considerations and Alternatives to the SCD Transformation
o Fact Table Loading
o SSAS Processing
o Using a Master ETL Package
• Advanced Data Cleansing In SSIS
o Advanced Derived Column Use
o Text Parsing Example
o Advanced Fuzzy Lookup and Fuzzy Grouping
o Fuzzy Lookup
o Fuzzy Grouping
o DQS Cleansing
o Data Quality Services
o DQS Cleansing Transformation
• Using the Relational Engine
o Data Extraction
o SELECT * Is Bad
o WHERE Is Your Friend
o Transform during Extract
o Many ANDs Make Light Work
o SORT in the Database
o Modularize
o SQL Server Does Text Files Too
o Using Set-Based Logic
o SQL Server Change Data Capture
o Benefits of SQL Server CDC
o Preparing CDC
o Capture Instance Tables
o The CDC API
o Using the New SSIS CDC Tools
o Querying CDC in SSIS
o Data Loading
o Database Snapshots
o The MERGE Operator
• Accessing Heterogeneous Data
o Excel and Access
o 64-Bit Support
o Working with Excel Files
o Working with Access
o Importing from Oracle
o Oracle Client Setup
o Importing Oracle Data
o Using XML and Web Services
o Configuring the Web Service Task
o Working with XML Data as a Source
o Flat Files
o Loading Flat Files
o Extracting Data from Flat Files
o ODBC
o Other Heterogeneous Sources
• Reliability and Scalability
o Restarting Packages
o Simple Control Flow
o Containers within Containers and Checkpoints
o Variations on a Theme
o Inside the Checkpoint File
o Package Transactions
o Single Package, Single Transaction
o Single Package, Multiple Transactions
o Two Packages, One Transaction
o Single Package Using a Native Transaction in SQL Server
o Error Outputs
o Scaling Out
o Architectural Features
o Scaling Out Memory Pressures
o Scaling Out by Staging Data
o Scaling Out with Parallel Loading
• Understanding and Tuning the Data Flow Engine
o The SSIS Engine
o Understanding the SSIS Data Flow and Control Flow
o Handling Workflows with the Control Flow
o Data Processing in the Data Flow
o Memory Buffer Architecture
o Types of Transformations
o Advanced Data Flow Execution Concepts
o SSIS Data Flow Design and Tuning
o Data Flow Design Practices
o Optimizing Package Processing
o Troubleshooting Data Flow Performance Bottlenecks
o Pipeline Performance Monitoring
• SSIS Software Development Life Cycle
o Introduction to Software Development Life Cycles
o SDLCs: A Brief History
o Types of Software Development Life Cycles
o Versioning and Source Code Control
o Subversion (SVN)
o Team Foundation Server, Team System, and SSIS
• Error and Event Handling
o Using Precedence Constraints
o Precedence Constraint Basics
o Advanced Precedence Constraints and Expressions
o Event Handling
o Events
o Using Event Handlers
o Event Handler Inheritance
o Breakpoints
o Error Rows
o Logging
o Logging Providers
o Log Events
o Catalog Logging
• Programming And Extending SSIS
o The Sample Components
o Component: Source Adapter
o Component: Transform
o Component: Destination Adapter
o The Pipeline Component Methods
o Design-Time Functionality
o Runtime
o Connection Time
o Building the Components
o Preparation
o Building the Source Component
o Building the Transformation
o Component
o Building the Destination Adapter
o Using the Components
o Installing the Components
o Debugging Components
o Design Time
o Building the Complete Package
o Runtime Debugging
o Upgrading to SQL Server2
• Adding a User Interface To Your Component
o Three Key Steps for Designing the UI: An Overview
o Building the User Interface
o Adding the Project
o Implementing IDtsComponentUI
o Setting the UITypeName
o Building the Form
o Extending the User Interface
o Runtime Connections
o Component Properties
o Handling Errors and Warnings
o Column Properties
o Other UI Considerations
• External Management and WMITask Implementation
o External Management of SSIS with Managed Code
o Setting Up a Test SSIS Package for Demonstration Purposes
o The Managed Object Model Code Library
o Catalog Management
o Folder Management
o Environments
o The DTS Runtime Managed Code Library
o SSIS Deployment Projects
o Parameter Objects
o Server Deployment
o Executing SSIS Packages Deployed to the SSIS Catalog
o Environment References
o Package Operations
o Application Object Maintenance Operations
o Package Operations
o Package Monitoring
o Project, Folder, and Package Listing
o A Package Management Example
o Package Log Providers
o Specifying Events to Log
o Programming to Log Providers
o SQL Server2 Operation Logs
o Package Configurations
o Creating a Configuration
o Programming the Configuration Object
o Configuration Object
o Windows Management Instrumentation Tasks
o WMI Reader Task Explained
o WMI Data Reader Example
o WMI Event Watcher Task
o WMI Event Watcher Task Example
• Conclusion
CASE STUDY COVERING ALL THE ABOVE TOPICS
SSRS Training: Introduction to SQL Server Reporting Services
Duration: 2 days
SSRS Training Prerequisites
All attendees must have a general familiarity with databases prior to the class.
Working knowledge of Microsoft SQL Server or Microsoft Office applications is beneficial.
Hands-on/Lecture Ratio
This SSRS training is 70% hands-on, 30% lecture, with the longest lecture segments lasting for 10 minutes.
SSRS Training Materials
All attendees receive a copy of SQL Server Reporting Services for Report Writers.
SSRS Training Objectives All attendees will learn to:
• Use SSRS tools
• Work with solutions and projects
• Design basic reports
• Enhance reports with formatting, images, and charts
• Effectively group and summarize data
• Work with parameterized reports
• Utilize Report Builder SSRS Training Outline
• SSRS Overview and Tools
o Overview of Reporting Services features
o General Report Server concepts
o Report concepts
o SSRS tools overview
o Using SQL Server Documentation
• Working with Solutions and Projects
o SQL Server Data Tools (SSDT)
o Understanding Solutions and Projects
o Working with the Visual Studio Interface
• Creating Basic Reports
o What is a Data Source?
o What is a Data Set?
o Using the Report Wizard
o What is Tablix?
o Creating a tabular report
o Creating a list report
o Understanding and creating a matrix report
• Formatting
o Rich text formatting
o Managing text box properties
o Drawing lines and boxes
o Inserting images
o Headers and Footers
o Adding a document map to a report
o Rendering reports
o Troubleshooting report rendering issues
• Using custom expressions
o Understanding expressions
o Where can expressions be used?
o Working with report variables
o Enhance formatting by using expressions
o Understanding Lookup, LookupSet, and Multilookup functions
• Summarizing and Sorting
o Creating Groups
o Enabling drill-down
o Using custom expressions and aggregations
o Creating aggregates of aggregates
o Enable interactive sorting
• Add Flexibility with Parameters
o Report parameters vs Query parameters
o Use parameters with SQL queries and stores procedures
o Manage report parameter properties
o Work with cascading parameters
o Sort based on a parameter setting
o Filtered reports
• Adding enhanced report items
o Add charts to a report
o Add a gauge to a report
o Add a map to a report
o Understanding data bar and sparkline report items
o Working with subreports
• Using Report Manager
o Navigating Report Manager
o Understanding permissions
o Managing reports
• Using Report Builder for SQL Server
o What is the difference between click once and stand-alone installer?
o Install stand-alone version
o Using the Report Builder tool
o Creating data sets
o Using the wizards provided
o Opening existing reports
o Report Templates
o Creating reports without a wizard
o Working with report parts
• Conclusion
SSAS Training: Introduction to SQL Server Analysis Services Duration: 2 days
SSAS Training Prerequisites
All SSAS training attendees should have prior experience with SQL Server . Hands-on/Lecture Ratio
This SSAS training class is 50% hands-on, 50% lecture, with the longest lecture segments lasting for 20 minutes.
SSAS Training Materials
All SSAS training students receive a copy of Microsoft Press' SQL Server Analysis Services Step-by-Step.
SSAS Training Objectives All attendees will learn to:
• Perform OLAP modeling
• Use SSAS inside of BIDS
• Store and aggregate data cubes
• Write multi-dimensional expressions (MDX)
• Administer SSAS
• Use SSAS in conjunction with reporting clients and Microsoft Office SSAS Training Outline
• What Is Microsoft Business Intelligence?
o Define Business Intelligence
o Understand the Cube Structure
o Deploy and View a Sample Cube
o View a Cube by using Excel
o View a Cube by using SQL Reporting Services
• OLAP Modeling
o Understand Basic OLAP Modeling (star schema)
o Understand Dimensional Modeling (stars and snowflakes)
o Understand Measure (fact) and Cube Modeling
o Model with SQL Server Business Intelligence Development Studio (BIDS)
• Using SSAS in BIDS
o Understand the Development Environment
o Create Data Sources
o Create Data Source Views
o Create Cubes by using the Cube Wizard
• Intermediate SSAS
o Learn how to Create Key Performance Indicators (KPIs)
o Discover how to Create Perspectives
o See how to Create Translations for Cubes and Dimensions
o Review the three SSAS Action Object Types: Regular, Drillthrough, and Reporting
• Advanced SSAS
o Work with Multiple Fact Tables and the Dimension Usage Subtab in BIDS
o Explore Advanced Dimension Types
o Learn how to use the Business Intelligence Wizard
o Understand Writeback in Dimensions
o Review Semi-Additive Measures in OLAP Cubes
• Cube Storage and Aggregation
o View Aggregation Designs
o Customize Aggregation Designs
o Implement Proactive Caching
o Use Relational and SSAS Partitions
o Customize Cube and Dimension Processing
• Introduction to MDX Queries
o Understand Basic MDX Syntax
o Use the MDX Query Editor in SSMS
o Understand Common MDX Functions and Tasks
o Review MDX Functions New to SSAS
• MDX Expressions
o Understand the Calculation Subtab
o Learn how to add Calculated Members
o Explore how to add MDX Script Commands
o Discover how to add .NET Assemblies
• Introduction to Data Mining
o Understand Data Mining Concepts
o Review the Algorithms that SSAS Includes
o Consider Data Mining Clients
o Understand Mining Structure Processing
• SSAS Administration
o Implement SSAS Security
o Implement XMLA Scripts in SSMS
o Deploy and Synchronize Databases
o Understand SSAS Database Backup and Restore
•
• Advanced Administration and Optimization
o Implement SSIS to Manage SSAS Databases
o Explore Clustering
o Explore Scalability Options
o Understand Performance Tuning and Optimization
• Introduction to SSAS Clients
o Design Reports using Reporting Services
o Design Reports by using Report Builder
o Implement Excel 2007 Pivot Tables and Charts
o Use Excel 2007 as a Data Mining Client
o Review Microsoft Office SharePoint Server 2007
• Conclusion
CASE STUDY COVERING ALL THE ABOVE TOPICS
