SQL 2000 Server DTS Data Transformation Services - SQ2D

Description

Data Transformation Services (DTS) is a set of tools that allow data to be modified and moved between a wide variety of databases. In the SQL Server 2000 DTS training course from ATS, database developers and administrators will learn how to use DTS with SQL Server. Expert instructor Leslie Koorhan demonstrates how to transfer data from SQL Server to other databases and the use of package components – tasks, steps and connections. At the conclusion of this course you will know how to incorporate DTS into any automated data transfer process.

Prerequisites: Understanding of relational databases required. Previous database design experience recommended. Data warehouse knowledge helpful.

Course Outline

Module 1

Introduction
CD Tour Database
Functions Database
History Design Issues
OLTP Normalized Databases

Transactional Databases

Indexing Index Issues
Index Dilemma Double
Databases Latency
Retrieval Database
Analysis Database

Data Warehouse
Attributes Data Marts
Data Mart Schema Fact
Table Dimension Tables
Hierarchies and Levels

Schemas and Data
Marts
Star Schemas Snowflake
Schemas Data Mart
Structure Creating a
Data Mart Populate a
Data Mart Staging Area

OLAP Cubes
Structure Storage
Modes Partitions
Aggregation Design
Virtual Cubes

DTS
Overview Solutions
Components In the Data Mart
In OLAP With Data Analysis

DTS Import/Export
Wizard
Wizard Startup Wizard
Functionality Wizard
Applications Wizard
Steps Creating a Plan

Using DTS Wizard
Database Setup
Planning Import Select
Source Destination Copy
Options Save DTS
Package Review
Executed Package Verify

Object Transferring
SQL to SQL Transfer
Process Object Copy

Prototype Package
Designing Package
Column Mapping
Dimension Load Use
Query Select/Map
Destination Map
Columns Save/Verify
View Package

Module 2

DTS Package
Components
Tasks/Steps
Properties/Variables
Package Designer
Designer Toolbars
Design Sheet

DTS Connections
Connection Component
Connection Sources
Connection Types
Connection Properties
Connection Usage
Parallel Data Loads
Create Source
Create Destination

Data Link Connections
Connection Maintenance
Change Connections
Create Data Link File
Set Properties
Modify Package

DTS Tasks
Tasks Transform Data
Manage Data Job
Functions Task Plan
Create Task Setup
Transformation Create Delete

DTS Steps
Steps/Precedence Set
Precedence Workflow
Properties Package
Organization Table
Verification

Bulk Insert Task
Overview Create Bulk
Insert Task Batch Size
Performance Options
Advanced Options

Format Files
Overview Structure
Create Format File Use
Bulk Insert Generate
Format File

Executing Bulk Insert
Bulk Insert Issues
Staging Tables Bulk
Insert Concepts Use
Staging Table Run/Save
Package Use Format File
Run/Verify

Module 3

Execute SQL Tasks
Considerations
Parameterized Queries
Add Global Variables
Input Parameters
Output Parameters
Using SP with DTS
Create Package
Run/Verify

Transform Data Task
DTS Data Pump Data
Pump Process/Users
Setup for Transformation
Create Transform
Task Transformation Options
Column Mapping Middle of String

Data Type Conversion
Transformation Flags
Uppercase String
DateTime Conversion
Test Transformation

Error Handling
Overview Configure
Data Movement SQL
Server Setting Setup
Error Reporting View
Exception Report Run with Error
Set Batch Size to 0

Transform Script
ActiveX Script
Transform Scripts
Script Languages
Components/Constants
Script Management
Script Performance
Script Plan
Skipping Row

Advanced Transformation
One Row to Many Build Connections
Select Task Properties
Splitting Rows Counter
Variable Setup Workflow

Lookup Queries
Overview Best Practices
Query Plan
Import Mapping Tables
Create Connections
Create Lookups

Multi-phase
Data Pump Overview
Phases Usage

Module 4

Data Driven Query
Task Design Usage
ActiveX Script Planning
Update with Data Query
Destination and Source
Create Package
Update Query

Storing DTS Packages
Package/Version GUIDs
Save DTS Package
Load/Edit Package
Meta Data Services
Structured Storage File
Visual Basic Format
Secure Packages
Data Lineage Metadata
Meta Data Services

Data Lineage
Create Package Set
Properties Save/View
View Meta Data Info

Package Execution
Package Behavior
dtsrun.exe
Property Parameters
Run Package
Alternative Actions
dtsrunui.exe

Modular Packages
Overview
Create Modular Package
Failure Options
Logging Options
Modular Design Build
Outer Package
Add Inner Package

Property Management Overview
Disconnected Edit
Connected Edit
Steps/Task Edit
Dynamic Property Tasks
Add/Edit Assignment
Dynamic Task Practices
Connection Challenges

Price £700 (Bundle of 4)

Complementary Courses
SQ2A - SQL Server 2000 Admin
SQ2D - SQL Server Implementing Data Base Design
SQD1 - SQL Server for Developers Part 1
SQD2 - SQL Server for Developers Part 2
SQ2D - SQL Server for Developers Part 3
SXM2 - XML SQL Server 2000

<<Back <<Contact Us