Moving DTS Packages from SQL 7 to SQL 2000

  • I am moving a couple of databases from SQL 7 on old machine to SQL 2000 on a new machine.

    I can move the user databases using attach and detach.

    I need to move about 600 DTS packages. Whats the easiest way of doing this? I dont want to open each package and then save them to the new server.

    Also the server names will be different, any way to change the connection settings in all the DTS packages in one go?

  • There's a free tool available here: http://www.sqldts.com/242.aspx that I like for copying a lot of packages at once.

    Greg

  • Thanks Greg,

    I read somewhere that opening SQL 7 dts packages in SQL 2000 designer can cause the packages to become corrupt.

    any idea if this tool (DTSBackup2000) will transfer my packages safely from SQL 7 to SQL 2000?

    Also 1 more question, How do i change the connection properties for all 600 packages as the new and old server have different names (i want to replace the old servername with the new servername in all 600 packages)?

  • I haven't heard of corruption problems in SQL 2000 and I haven't used DTSBackup2000 to copy packages created in SQL 7.0, but I don't know why it wouldn't work. You could try it with a single package and see if it moves it correctly.

    As for your second question, I seem to recall that the only way to make simultaneous changes in multiple packages is to save them as vb files and do a global change. There isn't a way to do it in SQL Server itself.

    Greg

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

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