3 Ways of Migrating DTS to SSIS 2005

Options for migrating DTS to SSIS 2005 are -

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, restartabililty
SSIS Service package enumeration and management
- Embed legacy package in 2005 package for easier deployment.
- Note – 32-bit only

Migration Wizard

- Not going to be 100% - Different Codebase
“Best Effort” migration
Creates new SSIS package; leaves original in place.
Migrate what we can -
Variables and control flow
Most tasks
If we cant Migrate, we wrap what we can not -
Complex Data Transformation Tasks
SSAS Tasks
Some constructs not supported
Transactions – We use common CS transaction flow. Old doesn’t work.
Dynamic Property Task – we rewritten obj model. Used browse and set.
We perform same functionality but we now use something differing.
Access to old API; script on step

Upgrade Advisor

- Run before upgrade. Point it at 2000 Install
- Works on server, local or remote, or on file system.
- Upgrade Advisor analyzes DTS packages and reports on upgrade and migration issues.
- Return to report later to review, check off fixes.
- Note:
Only works on DTS 2000 server
Overwrites report