Copying DTS packages from SQL 7.0 to SQL 2000

[Katyan]

Hi,

I have a number of DTS packages and jobs that I want to COPY from a SQL 7.0 Server to a SQL 2000 server. I ran the utility called DTS backup 2000, to copy the packages, however this process caused all the jobs on my SQL 7.0 server to fail on the next run. I got error messages saying that the DTS packages could not be found, even though they were all in the Repository. I had to resort to restoring a copy of the msdb database to get the jobs to work.

So is there another way to move packages and jobs between servers? Is there a table I can copy from the SQL 7.0 install to the 2000?

Thanks in advance.

[skhanal]

I'm not sure whether DTS packages are compatible. But you can definitely try.

You can open each package and Save As to your SQL 2000 or save the DTS package as structured file and open it in SQL 2000 and save it.

[rmiao]

And you can use dts transfer job task to copy sql jobs.

[Katyan]

Thanks for the feedback, however I'm encountering a new problem when I save the SQL 7.0 packages on the 2000 install. When I open the package to change the server name, the application crashes after I save and close the DTS package. I have to resart SQL each time. Any ideas what could be happening? I just installed SP3a on the SQL server and now I get the following error msg when I try to access the package: I am trying to save the packages using SQL 2000 Enterprise Manager, but after installing SP3a on the SQL 2000 server the problem seems to have gotten worst. When I try to open the SQL 7.0 Server's DTS packages to save them I get the following error.

Error string: 'ServerProperty' is not a recognized function name.
Error source: Microsoft OLE DB Provider for SQL Server.

Any ideas?

[rmiao]

How did you save sql7 packages in sql2k? Use structured file?

[Katyan]

I have not actually abandoned the upgrade to SQL2000 and reinstalled a copy of SQL 7.0 which is the version I have on the server I am moving the packages from. A similar problem exist as when I try to make changes to the packages now that they are on the new server and try to save them the application crashes. I've installed SQL 7.0 running SP4. But the problem continues

[Katyan]

Sorry I meant to say that I have actually abandoned the upgrade to SQL2000 and reinstalled SQL 7.0

[rmiao]

Which server crashed? Sql7 or sql2k? How did you move package?

[Katyan]

I installed SQL 7.0 SP4 on the Windows 2000 server and restored the Msdb database from the old server to the new install. That's how the jobs and packages were restored to the new server

[rmiao]

I thought you want to move packages from sql7 to sql2k.