DTS migration project.

In my current project we have to migrate a couple of hundreds DTS packages, jobs and other stuff from a SQL server 2000 Cluster to SQL Server 2008 cluster. For certain reasons we decided to take two steps:

• Phase 1 : migrate DTS packages to a legacy server (SQL Server 2008 with DTS backwards compatibility components)
• Phase 2 : migrate DTS packages to a new environment with SQL Server 2008 SSIS. A complete rebuild to Integration services.

So we divided the project in the following steps:
- Global analysis current situation.
- Global analysis desired situation Phase 1
- A Proof of concept for learning.
- Per system part

• Plan
• Migrate
• Validate

- Global analysis desired situation Phase 2
- A Proof of concept for learning.
- Per system part

• Plan
• Migrate
• Validate

The system part are parts of processes that have a strong cohesion with the other packages in the systempart and loosely coupled with DTS packages in other system parts. Per system part we created three main steps:

- Plan : In these steps we have to gather information about DTS packages, applications, jobs, linked server, access applications, system owner, etc etc. This information is processed in a migration plan. A DTS/databases and job/database matrix will be used for impact analysis
- Migrate. In this step the migration takes place. The system owner is notified that the software is migrated and the software is copied to a development environment, changed and deployed in the production environment, the system owner accepts the software and the last step will be evaluation of the best practices
- Validate. In this step the production is checked and validated by the system owner, The projectmanager is notified and lessons learned evalution is done.

We hope to start soon with a POC for a lessons learned. I'll blog about this laterss..

Greetz,
Hennie