March 13, 2008 at 2:56 pm
Hello,
We are trying to migrate from SQL Server 2000 to SQL Server 2005. I would like to know what all steps are involved
and if there is any easy documentation available. I think DTS's are not supported in 2005 so how easily can we
migrate them to 2005. We have more than 4000 DTS jobs in place which would have to be migrated.
Any help would be greatly appreciated...
Thanks,
Ramesh.
March 13, 2008 at 4:04 pm
This link may shed some light on your DTS migration
http://www.sqlservercentral.com/articles/Design/migratingdatawarehousesystemstosqlserver2005/2501/
It really depends what your DTS packages do but this article will help.
Gethyn Elliswww.gethynellis.com
March 13, 2008 at 4:35 pm
hi, the majority of dts's do migrate...but test and test again as sneaky bugs i am still finding and reporting to MS.
to migrate a whole dB..easiest is to backup 2000 db and restore to 2005, quicker option may be to detach and attach but test both...and the upgrade advisor may help you with any major problems.
with DTS, save as a storage file,then in 2005 import dts into Legacy folder (i can expand if you need), then tweak and test the dts - especially if you have migrated servers also 9 you will need to install the 2005 dts componant - i will post the link tomoz). Then when you are happy right click the legacy dts package and select 'migrate', this will transfer your dts to 2005. Choose where you want it stored msdb or file system. once there you can schedule your dts through a new agent job...
be weary of some issues with file flat file destinations- have found a few bugs/issues already, and if the dts's are simple just re-write them in ssis then you know they are ok.
** oh and make sure when you migrate you create a migration log file -as lots of interesting comments printed in there next to your dts elements!
Oraculum
March 13, 2008 at 4:59 pm
Good advice from oraculum and a good article reference from Ellis. Check out the articles here for more advice on moving to SQL 2005: http://www.microsoft.com/sql/solutions/upgrade/default.mspx
Greg
March 14, 2008 at 3:32 am
Ramesh,
as stated in my reply last night, you will need to download Microsoft SQL Server 2000 DTS Designer Components. You can do that from here http://www.microsoft.com/downloads/details.aspx?FamilyID=50b97994-8453-4998-8226-fa42ec403d17&displaylang=en
Scroll down the feature pack page until you see a link as below, then just download the .msi package called sqlserver2005_dts.msi
Microsoft SQL Server 2000 DTS Designer Components
The Microsoft SQL Server 2000 Data Transformation Services (DTS) package designer is a design tool used by developers and administrators of SQL Server 2005 servers to edit and maintain existing DTS packages until they can be upgraded or recreated in the SQL Server 2005 Integration Services package format. After installing this download, SQL Server 2005 users can continue to edit and maintain existing DTS packages from the Object Explorer in SQL Server 2005 Management Studio and from the Execute DTS 2000 Package Task Editor in Business Intelligence Development Studio, without needing to reinstall the SQL Server 2000 tools. The DTS package designer in this download was formerly accessed from the Data Transformation Services node in SQL Server 2000 Enterprise Manager.
Audience(s): Customer, Developer
X86 Package (SQLServer2005_DTS.msi) - 5088 KB
Oraculum
December 4, 2009 at 4:06 pm
how to copy DTS packages from one server to another(2000 to 2005)
December 4, 2009 at 5:33 pm
If you have a question, please start your own thread with the "Add Topic" button.
December 4, 2009 at 10:46 pm
sureshdeenu (12/4/2009)
how to copy DTS packages from one server to another(2000 to 2005)
Yes, better to start a new thread, not hijack this one. See my reply in this other thread on DTS migration:
http://www.sqlservercentral.com/Forums/Topic829138-146-1.aspx
December 7, 2009 at 11:02 am
Hi Guys, I am trying to migrate all the sql agent jobs from 2000 to 2005, can somebody give me step by step process? thanks in adavance
December 7, 2009 at 11:17 am
sureshdeenu (12/7/2009)
Hi Guys, I am trying to migrate all the sql agent jobs from 2000 to 2005, can somebody give me step by step process? thanks in adavance
Have you read what Steve and Homebrew suggested? Start a new thread.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply