Transcript DTS xChange Product Overview

DTS Conversion to SSIS
Conversion Best Practices
Mike Davis
([email protected])
About the Speaker
•
•
•
•
BI consultant Specializing in SSIS, SSRS
.Net Developer
Four years experience with SQL Server
Currently developing new products with
Pragmatic Works for SQL Server
• Blog on Pragmaticworks.com
Why choose SSIS?
• 64bit support
• Parallel in-memory multi buffer architecture
helps to load data several times faster than DTS.
• Logging, Configuration, CheckPoint etc.
• Source Safe Integration because everything is
XML
• Many new Tasks (e.g. Script Task, For Each
Loop, XML Task etc.) that replace need for
ActiveX
Less Maintenance… Faster Development… Faster Performance
DTS vs. SSIS Speed Test
• On 32 bit dual core machine
• Pulling 1 million rows out and writing to SQL
table with no transformation
• SSIS 65% + faster than DTS
• Adding transformation would add more SSIS
advantage
Average
Runtime
(seconds)
DTS
SSIS SQL
Server
Destination
SSIS OLE DB
Destination
33.2 s
11.3 s
12.3 s
Project Options
• Run DTS in 2005 or 2008
▫ Missing the package logs
▫ Runs under 32 bit
• Upgrade using MS Wizard
▫ Not compatible with most package
• Upgrade using DTSxchange
▫ Minutes per package
• Starting from scratch
▫ About 3-5 hrs per package
Demo
• Running DTS package in SQL Server 2005/2008
Microsoft Package Upgrade Wizard
• Built into SQL Server 2005/2008
▫
▫
▫
▫
▫
Does not handle ODBC
Only handles a few types of text file use cases
No Dynamic Properties Task
No UDL or legacy database support in data pump
Packages only have about a 20% of working
What is Involved in Upgrading?
• Scope and Number of Packages
• How long will it take you to migrate each type of
task
• How to migrate, Manual or Tool
• Upgrade the ActiveX Script Task logic, Manual
• Test, test and test
Microsoft Package Upgrade Wizard
• Demo
Feature Highlights
• Profiles DTS packages to help with a conversion project
plan
• Rapidly converts DTS Packages to SSIS (2005 or 2008)
and applies SSIS best practices
• Converts tasks that are not handled by the existing SQL
Server conversion wizard
• Handles Flat files Properly
• Shows Warnings not just success
DTS xChange Profiler
DTS xChange Migration
A Few of the Conversion Rules
•
•
•
•
•
•
•
Support for migration of children packages
Creates a robust Auditing framework
Consolidate Connections in Connection Manager
Create configuration files automatically
Create package transactions
Checkpoints
NULL handling
Other Advantages
•
•
•
•
•
•
Handles text files properly
Handles ODBC for a source
Migrates Dynamic Properties Tasks
Advanced profiler to estimate your project
Full validation of the output of the migration
92% package success rate in customer
migrations over thousands of packages
Built-in Reports
ActiveX Script Migration
• All tools mentioned migrate DTS ActiveX to
ActiveX in SSIS
• ActiveX migrates to SSIS but you would not
want to keep it there and it may not run
• Need for ActiveX Script Task has been replaced
with built-in, easy to maintain SSIS tasks
▫ File System Object = File System Task
▫ Mail objects = Send Mail Task (now has SMTP)
▫ ADO objects = Execute SQL Task
Summary
•
•
•
•
MS Wizard
Profiler
DTSxchange
ActiveX Script conversions
For Questions Email :
• [email protected]
Next Steps
• Profiler is free at DTSxchange.com
• Download a free trial of DTSxchange to convert 3
packages
• Watch the demonstrations on DTSxchange.com
Giveaway
• A copy of Professional SSIS will be given away.
Questions
• Contact Pragmatic Sales Department
▫ [email protected]
▫ [email protected] (me)
• Thank you for attending