|
Click on a date to register
|
 |
Live@DL |
SQL-342 -
4 days -
Instructor-led (classroom)
SQL Server 2005 & 2008 Integration Services for Database Professionals
Price: 3,250
Table of Contents
Introduction
Audience
At Course Completion
Prerequisites
Microsoft Certified Professional
Exams
Student Materials
Course Outline
Introduction
During this 4-day seminar, you will learn the ins and outs of SSIS and how to apply its enterprise functionality to common DBA tasks and how to manage SSIS packages for deployment and execution. At the completion this course, you will understand how to create and develop new SSIS projects and packages, apply SSIS to file and data management, and administer SSIS for team deployment and production execution.
Return to Top
Audience
This course is intended for database professionals that are responsible for DBA activities related to data processing, data architecture planning, or SSIS administration.
Return to Top
At Course Completion
After completing this course, students will be know more about:
| • |
Learning SSIS Features and Functionality: The initial seminar modules will dive deep into the core feature set of SSIS to lay the foundation for leveraging the tool for data processing and management requirements. |
| • |
Applying DBA File and Data Responsibilities in SSIS: Building on the SSIS knowledge, the focus will turn to applying SSIS to common data and file processing tasks such as local and remote file operations, data import and export methods, and data cleansing. |
| • |
SSIS Administration and Development Lifecycle: The final modules cover managing deployment environments, package security, execution, ongoing administration, and optimization. |
| • |
Best practices for data processing and SSIS administration |
Return to Top
Prerequisites
The target audience for this session is IT professionals who want to learn the details of how to use SSIS to accomplish data integration and how to administer SSIS through the development lifecycle to production. This course is targeted at professionals with experience working with databases and database administration; this workshop requires no prior experience with SQL Server 2000 DTS or SQL Server 2005.
Return to Top
Microsoft Certified Professional Exams
No Microsoft Certified Professional exams are associated with this course currently.
Return to Top
Student Materials
The student kit includes a comprehensive workbook and other necessary materials
for this class.
Return to Top
Course Outline
DAY 1
Module 0 – Course Introduction, Integration Services in SQL Server 2005
SQL Server 2005 installation and tools, SSIS features and architecture, the role of SSIS for the DBA
Module 1 – Design Environment and SSIS Features and Concepts Overview
Working with SSIS Projects, Properties windows and viewers, Data Sources, Data Source Views, Connections, Package variables
LAB 1 – Creating an SSIS Project and Package
Module 2 – Applying Control Flow Containers, Tasks and Constraints
Control Flow Task review, Execute SQL Task and Parameters, Sequence Container, Looping Containers, Standard and Expression based Constraints
LAB 2 – Workflow Management in the Control Flow
Module 3 – Applying Data Flow Transformations and Adapters
Data Flow source and destination adapters, Data expressions and conversion transformations, Data association and correlation transformations (IE: Lookup, Merge), Data Paths and Data Viewers
LAB 3 – Working with Data in the Data Flow
DAY 2
Module 4 SQL Server Management Tasks
Import and Export Wizards, SQL Server Maintenance Plans in Management Studio (SSMS), SQL Server Management Tasks in the Control Flow
LAB 4 Transferring Database Objects, and SQL Server Maintenance Plans
Applying the SSIS Control and Data Flow to Common DBA Duties
Module 5A – Working with and Importing Files
Drilling into File manipulation components: FTP Task, XML Task, Bulk Insert Task, File System Task, For Each Loop Container
LAB 5A – FTP, Import, and Archive
Module 5B – Data Extraction Methods and Destination Optimization
Source Adapters, Data extraction practices, Destination Adapters, Data loading optimization
LAB 5B – Data extraction and loading
Module 5C – Data Cleansing, Text Mining, and Binary data
Applying the Fuzzy Lookup and Fuzzy Grouping transformations, Parsing words from text columns, importing and exporting BLOB data
Lab 5C – Cleansing dirty data, exporting image columns to files
DAY 3
Module 5D – Notifications, Windows Management Instrumentation, Event Handlers and File watching
Leveraging the Send Mail Task, Introduction to WMI, Installing the custom file watcher component
LAB 5D –Watching files and working with WMI
Module 5E (optional) – DTS Package Migration and Redesign
DTS Support within SSIS, Upgrade Advisor DTS support, DTS migration wizard functionality, Migration challenges and caveats, Re-architecting principles from DTS to SSIS
Lab 5E – Upgrading DTS packages and redesign
Package Administration and Management
Module 6A - Package Configurations and Logging
Planning your configuration environment, SSIS Configuration Types, SSIS Logging
LAB 6A –SSIS configurations, SSIS execution logging and reporting
Module 6B - Transactions, Snapshots, and Restartability
How and when to use checkpoints, Managing RDBMS transactions, Rollback considerations, Integrating Checkpoints with Transactions
LAB 6B – Implementing transactions and checkpoints
DAY 4
Module 7 - Coordinating Team Development and Deployment, Modular package Design
Team development environment, Source Control, Deploying Packages, SSIS Deployment Wizard, Master/Child package design
LAB 7 – Package deployment, the deployment wizard
Module 8 - Package Administration: Security, Execution, and Scheduling
SSIS Management features, DTExec command line execution, Package Security, SQL Server storage, SQL Server Agent, Proxy accounts
LAB 8 – Creating modular packages, scheduling packages with SQL Agent
Troubleshooting and Optimization
Module 9 – Troubleshooting and Optimizing SSIS Packages
Understanding the SSIS buffer architecture, Data Flow transformation types, SSIS Performance Monitor counters and tracking, Debugging packages, Optimization techniques
LAB 9 – Monitoring Package Execution, correlation to PerfMon, Counters Survey Monitor Database Performance (it would be great to have a hosed-up database for them to watch
Return to Top
|