migrating from 2000 to 2008 with DTS

Question

Post reply

migrating from 2000 to 2008 with DTS

Jeff-132889

Ten Centuries

Points: 1112
More actions
February 15, 2010 at 3:48 pm

#216771

Due to some changes beyond my control I am migrating some .mdf files from 2000 to 2008. They all attached to 2008 fine but I can't find my DTS packages anywhere. I understand there is a wizard to convert then and there are also some 3rd party tools?
Where do I find my DTS packages?
What is the best was to confert them ti SSIS?
TIA, Jeff

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Express12 SSCoach Points: 18514 More actions · Answer 1

Migrating DTS to SQL 2008

-------------------------

To port your SQL 2000 DTS packages onto your SQL 2008 instance, you'll want to backup up your SQL 2000 msdb then restore it onto your SQL 2008 instance as msdb_2000 (don't overylay msdb on your 2008 instance!)

Then:

INSERT INTO msdb..sysdtspackages

SELECT * FROM msdb_2000..sysdtspackages

You'll now have the full compliment of DTS pacakges available on your SQL 2008 instance and can view them (NO editing allowed) via SQL 2008's Mgmt Studio under the Management\Legacy\Data Transformation Services tree.

You can still execute the packages, as is, after you install the SQL 2000 DTS exe (you'll need to google its location)

Converting DTS to SSIS

----------------------

To convert DTS to SSIS, we've never had a great deal of luck using the DTS migration wizard -- primarily because we wanted to take full advantage of the new SSIS features (system environment variables, XML config files, SQL config table, dynamic portability of SSIS pkgs across environmments, etc)

We recommend that if you are new to SSIS, you take the SSIS tutorial (under HELP) and rewrite the pkg's from scratch. Once you get the hang of it, most packages can be rewritten in a timely manner.

Hope this helps a little.

BT

Jeff-132889 Ten Centuries Points: 1112 More actions · Answer 2

Thanks BT I'll give it a go today and let you know. I appreciate the instructions.

Jeff

Jeff-132889 Ten Centuries Points: 1112 More actions · Answer 3

Thanks for the help BT. I ended up re-writing the DTS is SSIS since this was the best way to learn. The one thing I can't figure out however is how to truncate the existing tables before I import new copies.

Jeff

Edit: Nevermind I fould an Execute SQL control flow object that is doing the job.