June 1, 2009 at 10:16 am
Hi,
We are doing Migration from SQL Server 2000 to SQL Server 2005 and we have many DTS packages (around 250) sitting on the SQL Server 2000 instance?
Could anyone tell me how to migrate (Steps) the DTS packages to SS2K5 without actually converting them to SSIS packages and run them as the Legacy packages?
Can we still use the packages with the databases set to the compatibility mode 90 or should we leave the databases in compatibility mode to 80 itself after the migration for the legacy DTS packages to function properly?
Apart from these should we do any other modifications for the legacy DTS packages to run properly?
Thank you,
Yours truly,
SQLBuddy.
June 1, 2009 at 12:15 pm
Here's how I've done it:
1. Run SQL Server Upgrade Advisor on SQL 2000 to see any changes needed in DTS packages
2. In SQL 2000 Enterprise Manager, save DTS packages as files
3. In SQL 2005 Management Studio, expand Management, Legacy, right-click on Data Transformation
Services and select Import Package File
4. Download and install DTS Designer components and the DTS Runtime from here: http://www.microsoft.com/downloads/details.aspx?FamilyID=df0ba5aa-b4bd-4705-aa0a-b477ba72a9cb&DisplayLang=en
I didn't have anywhere near 200 packages to deal with so saving the packages one at a time worked for me. You might want to search for a script that will save all packages in an instance to files or check out DTSBackup2000 here: http://www.sqldts.com/242.aspx
The packages will work with compatability level 90.
Greg
June 1, 2009 at 12:31 pm
Hi Greg,
Thank you very much for your very detailed answer. I was really confused. You have made it crystal clear.
Yours
SQLBuddy
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply