Upgrading SQL Server 2000 to 2005 or 2008 - DTS to SSIS

Question

Our office uses SQL Server 2000, and by and large it has served our needs well. I'm looking into the possibility of upgrading to SQL 2005 or 2008 because of the new features not found in 2000 (when I saw Index usage statistics which told me which Indexes were the most used and which were never used, that blew me away).

I'm not too concerned with how to move the databases from 2000 to 2005 or 2008 - I can just take a full backup and then restore the backup to the new sql installation. What concerns me are the existing DTS packages. We have dozens of them that we rely on every day. How difficult/easy is it to convert the existing DTS packages to SSIS? What are some of the 'gotchas' that I have to look out for?

Answer 1

Doing a backup & restore to do a database server upgrade has many gotcha's. It's best to do an in-place upgrade (you might want to take a full backup of the server first) & let the MS installer do the work. 2005 supports 2000 DTS packages as Legacy so there's no need to learn SSIS & convert all your packages unless you have the time & there isn't too many.

As for the backup & restore upgrade, don't forget about:

1. logins, you'll need transfer these, see KB246133 for more details
2. Any user and role permissions given to system databases will need to be re-created or transferred
3. linked server or remote servers will need to be re-setup
4. SQL Server Agent Jobs need to be exported-imported
5. Any Extended stored procedures need to be re-created
6. Replication will need to be re-setup
7. Log shipping will need to be re-setup & the standby server also upgraded

Answer 2

There is a migration tool for DTS packages, but I wouldn't use it. SSIS is so much better than DTS that you should:

1. Learn about SSIS. Get comfortable with it. Rejoice at having actual control flow.
2. Take the time to understand and document your existing DTS packges while you've still got the environment they're written for.
3. Use that understanding to write equivalent SSIS packages.

This idea will make no sense to you if you don't know SSIS. For instance, I was just thinking about how I used to have to do loops in DTS - that whole business with finding and modifying the precedence constraint you wanted to loop back to.

SSIS actually has a For Loop container that can loop over a set of numbers, over the rows in a rowset, over files in a folder, etc. No hacks with precedence constraints and little hidden scripts.