December 19, 2007 at 8:29 am
Would anyone like to share the approaches to migrate DTS packages from SQL Server 2000 to SQL Server 2005?
Any input will be greatly appreciated.
December 19, 2007 at 8:58 am
We have over 250 SQL 2000 DTS packages. When we migrated ALL of our prod SQL 2000 Servers to SQL 2005 64-bit, we opted to leave 1 behind and dedicate this single SQL 2000 server - to accomodate these 250 DTS pkgs. (DTS will NOT run on SQL 2005 64-bit processor though I believe it will run on 2005 32-bit)
For the most part - nothing changed - except for MUCH BETTER performance !!
- We modified our SQL 2000 DTS connections to reference the new SQL Server 2005 instances/databases.
- We previously used ASPsmartMail for eMail notify's (a 3rd party .dll) in our DTS ActiveX scripts which had to be removed - and supplemented w/ DTS SQL task using send_cdosysmail.
- Though the DTS packages are still defined on SQL Server 2000, the performance gains using SQL 2005 64-bit input/output connections were terrific -- usually 4x faster.
December 19, 2007 at 9:42 am
There is a runtime for DTS packages on 2005, so you can run them in that. I believe this is only 32-bit, but I thought a 64-bit version was coming.
You can try to migrate them. A partner of mine also has a tool that has a much higher success rate in converting packages. I haven't used it, but it seems to be doing a great job. ()http://www.pragmaticworks.com/products.htm
December 19, 2007 at 10:14 am
We saved DTS packages as files using DTSBackup2000 from http://www.sqldts.com/. In SSMS in the SQL 2005 instance, we imported the package files and saved them to SQL Server, then opened them and modified the connections. We downloaded the DTS 2000 runtime components and continue to run the packages as DTS. We haven't begun to convert them to SSIS yet.
Greg
Greg
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply