Transcript Upgrading DTS Packages to Microsoft SQL Server Integration

Wouter Smit
About the Speaker
Wouter has been working in the data
warehousing field for more than 10 years
MCITP Professional
Database Administrator
Database Developer
Business Intelligence Developer
Worked on a multitude of database platforms
Working for a company called 3fifteen
technology solutions
About this session
A presentation that was done at the North
American Tech-Ed
Done by Brian Knight
SQL Server MVP, and co-founder of
SQLServerCentral.com and JumpstartTV.com
www.PragmaticWorks.com
Written ten SQL Server books
Mapping DTS Knowledge and Skills to SSIS
Running DTS Packages in SQL 2005 or 2008
Upgrading DTS Packages
Upgrading ActiveX Scripts
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
DTS vs. SSIS Engine 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
Package needs to run from the destination server
Average
Runtime
(seconds)
DTS
SSIS SQL
Server
Destination
SSIS OLE DB
Destination
33.2 s
11.3 s
12.3 s
Designing Packages
DTS Designer
Designing Packages
Business Intelligence Development Studio (BIDS)
Comparing DTS to SSIS Objects
Transform Data Task
Comparing DTS to SSIS Objects
Data Flow Task
Connections
DTS Connections
Connection Managers
SSIS Quick Mapping
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 DTS xChange
Minutes per package
Starting from scratch
About 3-6 hrs per package conservatively
Running Packages Under 2005+
Need to download DTS Designer Components
(part of SQL Server 2005 Feature Pack)
Pro:
Delays the upgrade hurdle
Con:
Cannot create new packages (easily)
Logging views historically in SQL Server 2000 not
available
Runs in 32 bit mode only (WOW)
Support ceases in vNext
Common Errors
Need to Install the 2005 Backward Compatibility
Kit
2005 DTS Designer Components
In SQL 2008, copy two additional DLL files over
and RLL files
Running DTS Package in SQL Server 2005/2008
Microsoft Package Upgrade Wizard
Built into SQL Server 2005/2008
Pros:
Free
Works on simple packages
Cons:
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% chance of working
Microsoft Package Upgrade Wizard
Demo of MS Existing Wizard
Sample Upgrade Project Plan
Scope and Number of Packages
How long will it take you to migrate each type of task
Use a tool to migrate
Upgrade the ActiveX Script Task logic
Test, test and test
DTS xChange 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
Includes a SSIS logging repository and reports for trending and
alerting
Includes BI xPress for new SSIS packages
Monitor Packages
DTS xChange
Profile, Convert and Monitor
A Few of the Conversion Rules
Support for migration of children packages
Logging to SQL Server, Text Files or via Event Handlers
Consolidate Connection Managers
Create configuration files automatically
Create package transactions
Checkpoints
NULL handling
Other Advantages
Handles text files properly
Handles NULLs properly
Handles ODBC for a source
Migrates Dynamic Properties Tasks
Advanced profiler to estimate your project
Full validation of the output of the migration
94%+ package success rate in customer
migrations over tens of thousands of packages
Monitor Packages
Error/Warning Trend
Extract/Load Trend
Runtime Trend
Run Through Profiler
Run Through Package Converter
Show SSIS Auditing Framework
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
DeActiveX Script Conversions
BI xPress
Product changing names from SSIS xPress
Helps audit, notify, manage and deploy packages
Provides in code reusability in SSIS
Integrates with Business Intelligence Development
Studio (BIDS) and Management Studio
BI xPress
BI xPress
Please download a demo at:
[email protected]
Resources
www.microsoft.com/teched
www.microsoft.com/learning
Sessions On-Demand & Community
Microsoft Certification & Training Resources
http://microsoft.com/technet
http://microsoft.com/msdn
Resources for IT Professionals
Resources for Developers
www.microsoft.com/learning
Microsoft Certification and Training Resources
Complete an
evaluation on
CommNet and
enter to win!
© 2009 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries.
The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should
not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS,
IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.