1 / 27

Out With The Old In With the New

Out With The Old In With the New. Conversion Best Practices Devin Knight Senior BI Consultant (dknight@pragmaticworks.com ). Agenda. Mapping DTS knowledge and skills to SSIS Running DTS packages in 2005 or 2008 Upgrading DTS Packages Upgrading ActiveX Scripts. Why choose SSIS?.

rocio
Download Presentation

Out With The Old In With the New

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. Out With The Old In With the New Conversion Best Practices Devin Knight Senior BI Consultant (dknight@pragmaticworks.com)

  2. Agenda • Mapping DTS knowledge and skills to SSIS • Running DTS packages in 2005 or 2008 • Upgrading DTS Packages • Upgrading ActiveX Scripts

  3. Why choose SSIS? • 64bit support • Faster architecture • Logging, Configuration, CheckPoint etc. • Source control • More stuff

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

  5. Designing Packages • DTS Designer • Business Intelligence Development Studio (BIDS)

  6. Comparing DTS to SSIS Objects • Transform Data Task • Data Flow Task

  7. Connections • DTS Connections • Connection Managers

  8. Demo SSIS Quick Mapping

  9. Project Options • Run DTS in 2005 or 2008 • Missing the package logs • Runs under 32 bit • Upgrade using MS Wizard • Processes a limited set of simple packages • Upgrade using DTS xChange • Minutes per package • Starting from scratch • About 3-6 hrs per package conservatively

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

  11. Common Errors • Need to install the 2005 Backwards Compatibility Kit • 2000 DTS Designer Components • In 2008, copy two additional DLL files over and RLL files

  12. 64 Bit Gotcha

  13. Demo Running Packages in 2005/2008 Running in legacy mode

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

  15. Demo DTS Migration Wizard Conversion using existing wizard

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

  17. Profiling

  18. Gotchas to Catch Here • Providers • 3rd party tasks • Types of ActiveX scripting

  19. Demo • Free DTS Profiler tool.

  20. DTS xChange Feature Highlights • Profiles DTS packages to help with a conversion project plan • Coverts to 2005 or 2008 (R2) • Logging and auditing framework • Includes BI xPress for new SSIS packages • Have converted more than 300,000+ packages

  21. Why is it Better?

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

  23. Runtime Trend Error/Warning Trend Extract/Load Trend

  24. Demo DTS xChange Profile, Convert and Monitor

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

  26. Demo ActiveX Script Conversion Loop conversion

  27. The End so Soon? (yawn) http://www.bidn.com/blogs/devinknight @knight_devin dknight@pragmaticworks.com

More Related