SQL Server User Group Meeting

From SQL Server 2000 DTS to SQL Server 2005 SSIS Upgrade and Migration

• Pat Martin
• Senior consultant
• patmar@microsoft.com
• Microsoft Tech-Ed 2006

Session objectives 7 key takeaways

• Session Objectives
  • Explain the migration story for SSIS 2005
  • Describe tools and practices for migration
  • Provide guidance for current engagements
• Key takeaways
  • We will not break existing installs
  • Integration Services is first version of a new ETL product
  • Migration is not perfect
  • Redesign is a better option

Agenda

• From DTS to SSIS
• Upgrade Experience
• Support for Migration
• Migration Packages
• Guidance for DTS Users

Upgrade vs. Migration

• Two products – two questions
• Upgrade
  • How do I upgrade my server without disturbing existing DTS processing?
• Migration
  • How do I bring my existing DTS processing under the new SSIS model?

Upgrade Issues

• Remote server upgrades to SQL Server2 005
• Local server upgrades to SQL Sever 2005
• Continuing to maintain DTS [packages after upgrade
• Side-by-side vs. upgrade

Remote Server Upgrade

• Local DTS packages continue to access an upgraded remote server without interruption
• SQL Server 2000 Admin Tools will not connect to the upgraded server
  • Continue to edit DTS packages on remote file share without interruption
  • Install 2005 tools to edit DTS packages store in SQL Server 2005 msdb

Local Server Upgrade

• Tools install includes DTS 2000 runtime
• DTS packages on server, file system untouched by upgrade
• DTs packages on upgraded server remain available for execution and maintenance
• DTs agent jobs run without interruption
• Apps written to DTs API continue to work
• Server upgrade removes SQL Server 2000 Admin Tools
  • If you need legacy tools, install 2005 tools alongside 2000
  • Repository support withdrawn

Maintaining DTS 2000 Packages after Upgrade

• Use SQL Server 2005 Management Studio to manage DTS packages
• Access via Legacy node in Object Explorer
• Legacy UI components don’t ship with 2005
• Download DTS 2000 Designer Components

Side-by-Side vs. Upgrade

• Upgrade database Server?
  • Avoid updating remote DTS packages and applications by upgrading the database server
  • Run 2000 and 2005 processing in parallel with side-by-side install
• Upgrade Admin tools?
  • DTS and SSIS are essentially separate products
  • Missed…

Support for Migration

• Execute DTS 2000 Package Task
• Migration Wizard
• Upgrade Advisor

Execute DTS 2000 Package Task

• Use the Execute DTS 2000 Package task to invoke DTS packages from within an SSIS 2005 package
• Wrap legacy packages to take advantage of improved SSIS:
  • Looping and sequencing constructs
  • Package security
  • Event handling
  • Logging and error handling
• Embed legacy package in 2005 package for deployment

Package Migration Wizard

• “Best Effort” migration
• Creates new SSIS package leaving original in place
• Migrate what we can convert
  • Variables and control flow
  • Most tasks
• Wrap what we cannot convert
  • Complex Data Transformation Tasks (ActiveX script model)
  • Analysis Services Tasks
• Some constructs not supported
  • Transactions
  • Dynamic Property Task
  • Access old API; script on step

Upgrade Advisor

• Run before upgrade
• Works on server, local or remote, or on file system
• Upgrade Advisor analyses DTS packages and reports on upgrade and migration issues
• Return to report later to review, check off fixes
  • Requires DTS 2000 server
  • Overwrites report

Package Migration Cases

• Workflow
• Simple Data Transformation
• Complex Data Tran
• Self-modifying packages
• Dynamic Properties
• Custom Task

Workflow Packages

• Most tasks migrate seamlessly
  • FTP
  • Execute Process
  • Execute SQL
  • Bulk Insert
  • Copy Objects
  • Send Mail
  • Execute Package
  • MSMQ
  • Transfer Tasks
• Control Flow and Precedents migrate without issue
• Global Variables fully migrate
• ActiveX Script task may require fix-up if referencing old API

Simple Data Transformation

• DGS Data Transformation and SSIS Data Flow object models don’t map one to one
• Goal
  • Migrate all package form Import/Export Wizard

Complex Data Transformation

• Where migration not supported, migration wraps original taks in an Execute DTS 2000 Package Task
  • Script or other transforms
  • Multiphase pump, lookup, etc
• Redesign with SSIS 2005 transformation s for performance
• Add error handling for reliability

(here the battery died)