03 – DTS to SSIS

1. SQL Academy 2008 03 – DTS to SSIS Bob Duffy Senior Consultant MCA Database| SQL Ranger

2. Agenda • What’s the Problem ? • Upgrade Advisor • DTS Package Support • Migrating Packages • Package Redesign • Third Party Tools

3. Key Functional Differences

4. Migration Tools and Options • Upgrade advisor / dts X change • Scan DTS 2000 packages, report on migration issues • 1) Run dts package on Sql 2008 • DTS 2000 runtime engine • DTS 2000 package task • 2) Migration/Upgrade Wizard • Best efforts basis • 3) Third party Tools • Dtsxchange • 4) Re-Architect • All by Hand.. • 5) get Someone else to do it • Shameless plug;-)

5. Agenda • What’s the Problem ? • Upgrade Advisor • DTS Package Support • Migrating Packages • Package Redesign • Third Party Tool

6. Upgrade Advisor • Detects and Identifies potential Upgrade Issues • Reports Upgrade issues: • Password Protected DTS Packages • Unable to Scan Packages • Unregistered Tasks • Only Most Recent Version of Package • Identifies Package Migration Issues • Pre-Upgrade, Post Upgrade and Warnings • Rewrite • Replace recommendations on Encapsulation

7. Needs DTS 2000 runtime.

8. demo Upgrade Advisor 2008

9. Agenda • What’s the Problem ? • Upgrade Advisor • DTS Package Support • Migrating Packages • Package Redesign • Third Party Tools

10. DTS support in SQL Server 2008 • DTS runtime • Includes the DTS object Model • Includes the DTSRUN utility • Execute DTS 2000 Package Task • ActiveX scripting (for backward compatibility only) • DTS Package Migration Wizard • Upgrade Advisor • Packages can run in 32 Bit only • No 64 bit design-time or run time support for DTS.

11. Mapping DTSRUN options to DTEXEC Usage: DTExec /option [value] [/option [value]] ... . A hyphen (-) may be used in place of a forward slash (/). /CheckF[ile] [Filespec] /Checkp[ointing] [{On | Off}] (On is the default) /Com[mandFile] Filespec /Conf[igFile] Filespec /Conn[ection] IDOrName;ConnectionString /Cons[oleLog] [[DispOpts];[{E | I};List]] DispOpts = any one or more of N, C, O, S, G, X, M, or T. List = {EventName | SrcName | SrcGuid}[;List] /De[crypt] Password /DT[S] PackagePath /F[ile] Filespec /H[elp] [Option] /L[ogger] ClassIDOrProgID;ConfigString /M[axConcurrent] ConcurrentExecutables /P[assword] Password /Rem[ark] [Text] /Rep[orting] Level[;EventGUIDOrName[;EventGUIDOrName[...]] Level = N or V or any one or more of E, W, I, C, D, or P. /Res[tart] [{Deny | Force | IfPossible}] (Force is the default) /Set PropertyPath;Value /Ser[ver] ServerInstance /SQ[L] PackagePath /Su[m] /U[ser] User name /Va[lidate] /VerifyB[uild] Major[;Minor[;Build]] /VerifyP[ackageid] PackageID /VerifyS[igned] /VerifyV[ersionid] VersionID /W[arnAsError]

12. 32/64 bit considerations • No 64 bit design-time or runtime support • 64 bit data sources • Run64BitRuntime property • Execute DTS 2000 Package not 64 bit!

13. Editing Packages #1

14. Editing Packages #2 • Use DTS Designer from SSMS + BIDS • 1. To ensure that the DTS designer can be used in Management Studio: • a.    Copy the following files from the %ProgramFiles%\Microsoft SQL Server\80\Tools\Binn folder to the %ProgramFiles%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE folder: SEMSFC.DLL, SQLGUI.DLL, and SQLSVC.DLL. • b.    Copy the following files from the %ProgramFiles%\Microsoft SQL Server\80\Tools\Binn\Resources folder to the %ProgramFiles%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Resources\%lang_id% folder: SEMSFC.RLL, SQLGUI.RLL, and SQLSVC.RLL. For US English, for example, the lang_id subfolder will be "1033". • 2. To ensure that the DTS designer can be used in BIDS: • a.    Copy the following files from the %ProgramFiles%\Microsoft SQL Server\80\Tools\Binn folder to the %ProgramFiles%\Microsoft Visual Studio 9.0\Common7\IDE folder: SEMSFC.DLL, SQLGUI.DLL, and SQLSVC.DLL. • b.    Copy the following files from the %ProgramFiles%\Microsoft SQL Server\80\Tools\Binn\Resources folder to the %ProgramFiles%\Microsoft Visual Studio 9.0\Common7\IDE\Resources\%lang_id% folder: SEMSFC.RLL, SQLGUI.RLL, and SQLSVC.RLL. For US English, for example, the lang_id subfolder will be "1033".

15. demo Running DTS Packages

16. Agenda • What’s the Problem ? • Upgrade Advisor • DTS Package Support • Migrating Packages • Package Redesign • Third Party Tools

17. Migratable DTS Task Migrated SSIS Task Not Migratable DTS Task Not Migratable DTS Task DTS Package Migratable DTS Task Migrated SSIS Task DTS Package SSIS Package Migration Execute DTS Package Task

18. Migration issues • Simple data pumps migrated • Simple Workflow packages migrate well • Applications against DTS 2000 object model continue to work • Any issues are “encapsulated” (next slide)

19. Encapsulation • Tasks runs as a SQL 2000 Package executed within the ‘upgraded’ package • Rewrite is recommended • Will not take advantage of SSIS 2005 performance, logging or error handling • May occurs on: • ActiveX Scripts • Dynamic properties • Looping • Data Driven Queries • Data pumps with transformations • Analysis Services Task • Custom Tasks • Data Mining Prediction Tasks • Parallel Data Pump • UDL Connections • See BOL

20. “No Harm Done” DTS experience remains Staged Migration Install Completed • Best effort migration • Execute DTS 2000 task if necessary • Improved design time experience and Management Migration Wizard Completed Substantially improved experience and Management Breakthrough performance Utilize SSIS functionality

21. Planning for migration • Understand DTS usage today • Use the Upgrade Advisor • Try the migration wizard • Identify SSIS features for DTS constructs • Identify new architectural opportunities • Removing staging areas • Lookups and Merge Joins • Script Components • Aggregating and sorting in the data flow • Loading MOLAP directly • Event handlers

22. demo Migrating DTS Packages

23. Agenda • What’s the Problem ? • Upgrade Advisor • DTS Package Support • Migrating Packages • Package Redesign • Third Party Tools

24. New Architecture • SQL Server Integration Services(SSIS) service • SSIS object model • Two distinct runtime engines: • Control flow • Data flow • 32-bit and 64-bit editions

25. Why Redesign? • DTS is deprecated. Probably not in SQL 2010 • DTS is a pain to support/manage • Performance due to buffers and 64 bit • New features: • Data Flow and Control Separation • Lookups • Enhanced Debugging/logging • Deployment • More Data Sources • Better Scripting/Flexibility/Configuration • Less need for data scrubbing

26. demo SSIS Redesign

27. Agenda • What’s the Problem ? • Upgrade Advisor • DTS Package Support • Migrating Packages • Package Redesign • Third Party Tools

28. Profile • Find how many DTS packages you have and how Complex they are and calculate estimated time to convert DTS packages. • Find real usage of your DTS packages (Note: Only if they are scheduled through SQL Agent Job) • Search for packages with specific task type (e.g. Return all packages with ActiveX task) Convert • Migrate DTS Packages from SQL 2000 to SQL 2005/2008 with minimum efforts. • Add more value to converted package by applying best practices during migration e.g. • Standardize Naming Convention of Connections • Remove duplicate connections • Generate reusable connection architecture by creating XML Config files • Apply auditing framework to monitor errors/warnings and performance of package • Add CheckPoint, Transaction or Logging to converted packages • Convert UDL Connection, Dynamic Properties Task and Complex Datapump • Add NULL handling and Data Conversion automatically if required • Secure Packages by applying proper Protection level • Validate packages after conversion for any possible error/warning and suggest changes if required Monitor • Auditing framework added automatically to monitor your package activity • Several reports are provided to monitor Error/Warnings, Record Count and Performance of packages and tasks inside package

29. Find Time/Cost Saving Find Success Rate

30. 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 • 92% package success rate in customer migrations over thousands of packages

31. demo DTS X change

32. Benefits Summary

33. References • Fix DTS Designer Components in SQL 2008http://support.microsoft.com/kb/905417 • DTS X change DTS conversion toolhttp://www.pragmaticworks.com • ssisUNIT Unit Testing http://www.codeplex.com/ssisUnit • Known DTS Package Migration Issues • http://msdn.microsoft.com/en-us/library/ms143462.aspx

34. © 2008 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.