DTS Conversion to SSIS Conversion Best Practices Mike

DTS Conversion to SSIS Conversion Best Practices Mike Davis (MDavis@pragmaticworks. com)

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? • 64 bit support • Parallel in-memory multi buffer architecture helps to load data several times faster than DTS. • Logging, Configuration, Check. Point 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 Active. X 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 Active. X 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 x. Change Profiler

DTS x. Change 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

Active. X Script Migration • All tools mentioned migrate DTS Active. X to Active. X in SSIS • Active. X migrates to SSIS but you would not want to keep it there and it may not run • Need for Active. X 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 Active. X Script conversions For Questions Email : • MDavis@pragmaticworks. com

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 ▫ sales@pragmaticworks. com ▫ MDavis@pragmaticworks. com (me) • Thank you for attending