Corrupted DTS Package - Yes and No.

[SQLBill]

Background: I administer both SQL Server 2000 and 2005 servers. We have DTS packages running on both. Recently, I had an issue with a DTS package on a 2005 system. I couldn't open it and view the package. The solution was to install SQL Server 2005 SP1 on my desktop to update the client tools.

Issue: This seems to have affected my SQL Server 2000. Yesterday, I copied a developer's DTS package from the development system to the test server (all 2000 instances) and the DTS is now corrupt. It won't run on the 2000 server and anyone without the SQL Server 2005 SP1 update can't view it. Anyone with the SP1 installed can view it.

Has anyone else seen this behavior? Anyone have a solution besides....reimage my computer and not load SQL Server 2005 SP1?

-SQLBill

Posting advice: FAQ481-4875

 

[Catadmin]

Bill,

Is this the only copy of the package you have? Or do you have one in some sort of source control solution that you can scavenge?

If the later, I believe you have to install some backward compatibility tools on your SQL Server 2005 machine to edit it properly, but I'm not sure you'll be able to run it on SQL 2000 afterwards. I think you'll have to run it on SQL 2005.

http://msdn2.microsoft.com/en-us/library/ms143706.aspx

When I was on the Microsoft SQL 2005 webcasts, they told us over and over again that anything attached to SQL 2005 (like moving a DB to a 2k5 instance) or using the 2k5 tools to edit (not counting a 2000 instance that you rename tables with, etc.) gets upgraded to 2005 automatically.

What was the issue with the DTS package that you were forced to upgrade to look at it?



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[SQLBill]

The other package (that required the update) was on a SS2005 box. When I tried to open it I got an error saying something about needing something like 'DTS Web Component'. Searching the actual message on Microsoft's website led me to the requirement for SS2005 SP1 to be applied to the SS2005 client tools. Once I applied that, I was able to open DTS packages in SS2005 along with editting and moving them.

But, a side effect was that it's messing up any SS2000 DTS packages that I touch via my desktop SS2000 client tools.

I'll check out the link...however, one issue is that we need it to run on SQL Server 2000. The other issue is that I need a solution so that I can continue to work on DTS packages from my desktop without having to remote into the actual servers.

-SQLBill

Posting advice: FAQ481-4875

 

[Catadmin]

I had to reinstall SQL 2000 (client tools and all) after I installed SQL 2k5 on my PC because of other problems. Maybe that's all you need to do.

I've edited several DTS packages via EM on my desktop since then and had no corruption or SQL 2k5 issues.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[SQLBill]

To clarify.....I was discussing two different issues and two different DTS packages.

1. DTS package on a SQL Server 2005 box. Issue was that I couldn't open the package and edit it. This was solved by installing SQL Server 2005 SP1 on my desktop, which has both SS2000 and SS2005 client tools.

2. DTS package on a SQL Server 2000 box. Issue was that I needed to move this package from a dev box to a text box (both SS2000) and edit the test version (to update the server information). Even though I have SS2000 client tools on my desktop and used them, the DTS package was 'automagically' upgraded to be usable in SS2005. That makes it no longer usable in SS2000 AND anyone without the SS2005 SP1 on their client tools cannot view the package.

Solving issue 1 is what caused me issue 2.

-SQLBill

Posting advice: FAQ481-4875

 

[SQLBill]

I'll give reinstalling SS2000 client tools a try. Do you still have both versions on your desktop (SS2000 and SS2005 client tools)?


-SQLBill

Posting advice: FAQ481-4875

 

[Catadmin]

Yes, I do. And I have them both open at the same time during the day. It hasn't caused me any problems.


I must admit, I haven't heard of your particular problem until now, though I had heard of a similar one with SRS. If you open up an SRS 2000 report with SRS 2005 tools, it auto-upgrades the report and then won't work on SRS 2000. This is why I had to reinstall SQL2000 on my box after the initial SQL2k5 SP1 install.

There's also a hot fix for 2k5 SP1 that you might want to install before reinstalling 2000.

http://support.microsoft.com/kb/918644

Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[Catadmin]

Ack! I gave you the wrong hot fix page.

http://support.microsoft.com/kb/918222

is the correct one. Sorry for the confusion.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"