New Zealand's new Copyright Law presumes 'Guilt Upon Accusation' and will Cut Off Internet Connections without a trial. is against this unjust law - help us

A Space for All Things .NET Related

SQL Server User Group Meeting

By James Hippolite, in , posted: 12-Oct-2007 07:32

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

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


  • 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)

Other related posts:
GeekPost Weekly Newsletter Volume 4 Issue 7
MSDN Unplugged Roadshow Wellington
Right Sizing Agile Development - MSDN Magazine Webinar

Add a comment

Please note: comments that are inappropriate or promotional in nature will be deleted. E-mail addresses are not displayed, but you must enter a valid e-mail address to confirm your comments.

Are you a registered Geekzone user? Login to have the fields below automatically filled in for you and to enable links in comments. If you have (or qualify to have) a Geekzone Blog then your comment will be automatically confirmed and shown in this blog post.

Your name:

Your e-mail:

Your webpage:

JamesHip's profile

James Hippolite
New Zealand

Welcome to my technical blog. 

Here, I attempt to distill the Microsoft Certified Professional Developer knowledge I have accumulated since first qualifying MCP in 1996.  This blog started on 13 September 2007 as an off-shoot from my mixed up personal blog.  But it took a shot in the arm from Scott Hanselman's talk at TechEd New Zealand 08 "32 Ways To Make Your Blog Suck Less".