Download
dts conversion to ssis n.
Skip this Video
Loading SlideShow in 5 Seconds..
DTS Conversion to SSIS PowerPoint Presentation
Download Presentation
DTS Conversion to SSIS

DTS Conversion to SSIS

100 Views Download Presentation
Download Presentation

DTS Conversion to SSIS

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

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

  2. 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

  3. 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

  4. 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

  5. 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

  6. Demo • Running DTS package in SQL Server 2005/2008

  7. 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

  8. 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

  9. Microsoft Package Upgrade Wizard • Demo

  10. 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

  11. DTS xChange Profiler

  12. DTS xChange Migration

  13. 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

  14. 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

  15. Built-in Reports

  16. 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

  17. Summary • MS Wizard • Profiler • DTSxchange • ActiveX Script conversions For Questions Email : • MDavis@pragmaticworks.com

  18. Next Steps • Profiler is free at DTSxchange.com • Download a free trial of DTSxchange to convert 3 packages • Watch the demonstrations on DTSxchange.com

  19. Giveaway • A copy of Professional SSIS will be given away.

  20. Questions • Contact Pragmatic Sales Department • sales@pragmaticworks.com • MDavis@pragmaticworks.com (me) • Thank you for attending