1 / 20

DTS Conversion to SSIS

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

nodin
Download Presentation

DTS Conversion to SSIS

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

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

More Related