MIgrating DTS packages of SQL 2000 to SQL 2005

Hello All,

I am working on a migration project of my database(lets name it DB1), from SQL 2000 to SQL 2005. I have some DTS packages in my SQL 2000 database which also needs to be migrated to SSIS. These DTS packages currently interacts with other database(lets name it DB2) which is also in SQL 2000 and which does the data transmission. The real issue is that the other database(DB2) also has some DTS packages which also communicates with DB1 (the db to be upgraded) and so when i migrate my DB1 to SQL 2005, i will have to change my DB2 packages also (although the change will be minor). Now considering that in future I might also migrate my DB2 to SQL 2005 I wanted to know what will be the right approach to follow. One is to modify the DTS packages of DB2 to accmodate the change in connection and the other is to migrate the DTS packages of DB2 also to SSIS.

Can anyone help me with this one.

Thanks in Advance

Mitesh

Hi Mitesh,

Something to consider: You can migrate a SQL Server 2000 database (DB1) to SQL Server 2005 and continue to run it at a SQL Server 2000 Compatibility Level. The implication is you may be able to continue connecting to DB1 from the DB2 DTS packages as you do now - it will just be on a different instance of SQL Server. I'm not sure what your DB2 DTS packages do exactly, so it's difficult to make that statement with more confidence.

There's also an Execute DTS 2000 Package Task available in SSIS. You can execute your existing DTS packages from SSIS. I've used this (and the aforementioned Compatibility Level) before as part of a migration plan from SQL Server 2000 to SQL Server 2005. It's nice because I don't have to get everything converted before starting to use the new database engine.

Regarding migrating between DTS and SSIS: that's a tough one. I teach ETL with SSIS for Solid Quality Learning. One of the things I share with students is the fact that SSIS is not the new version of DTS - it's a replacement. Microsoft did not start with DTS and modify it - they rewrote the entire application. This has some bearing on how easy it is to migrate from DTS. I've seen very simple DTS packages migrate easily, but that's about it.

There's so much new under the hood, you will most likely want to rebuild - and perhaps redesign - your packages in SSIS to take advantage of some of the stellar performance gains.

Hope this helps,

Andy

|||

Hey Andy,

Thanks for the reply will surely consider your suggestion. Just one more thing, when i use the Execute DTS 2000 Package task of SSIS i will have to change my connection as my database is migrated to SQL 2005 on another server. Correct me if i am wrong

|||

Hi Mitesh,

Yes sir - you will have to change your connection to target another database, server, or both.

Hope this helps,

Andy