Corrupted DTS packages
-
I have inherited a SQL 2000 database that has several DTA packages that appear to have become corrupted. When I attempt to open them in Design Package I receive the error, "Error Source: Microsoft Data Transformation Services (DTS) Package, Error Description: Invalid Class String".
I desparately need to get these back. Is there some way to open and fix them? Can I restore just the DTS packages?
-
You'd have to restore a backup of msdb. I'd try restoring one on another instance and then save the packages out as structured storage files.
You also might try opening them from another client.
-
Tim,
Check this article and see if any of the situations and workarounds fit your circumstances. http://support.microsoft.com/kb/288910/en-us
Greg
Greg
-
These are packages that run as SQL Agent jobs; they fail there which is why I tried to open them. I've just had the back file restored from tape onto a local drive. How would I go about restoring this without damaging the existing databases on the server, or doesn't it matter with this one. There have been no changes to anything (structure-wise) on this platform.
-
You'll need to stop SQL Server Agent before restoring msdb. Otherwise, it's like restoring a user database. See "restoring msdb" in BooksOnLine.
If you have another instance, like a test or crash-and-burn server, where you can restore msdb, you could then just copy the packages back to the original instance.
BTW, did you recognize anything in the KB article as relating to your situation?
Greg
Greg
-
Yes. I need the SQL 2000 Client Tools. However, when I do get to look into the package it's empty.
-
If your packages have custom tasks you will get this message. If it is a custom task problem your MSDB is probably OK, all you need to do is register the tasks.
Custom tasks need to be registered on both the server machine, and on every client machine that wants to open DTS Designer for those tasks.
First, you need to find the components of the custom tasks, and copy them to the appropriate libraries. Hopefully the installation documentation for your application will give you some clues about this. On our DW system, there are some DLL files that need to be copied to the SQL TOOLS\BINN library, and OCX files that need to be in the Windows \SYSTEM32 library.
Having copied the components you need to register them. Hopefully I remember this right, as I do not have DTS Designer on this PC... Open DTS Designer for a new package, and select Tasks. Near the bottom of the list, select Custom Task. In that dialogue, enter the task name (it can be anything, but keep it consistant with your documentation), click on the ellipses and navigate to the TOOLS\BINN folder. Click on the appropriate DLL, then clck OK. Repeat this for all other custom tasks, then close DTS Designer. There is no need to save the package you were working in, this will not affect the registration.
You should now be able to open your problem DTS packages.
If you are porting DTS to SQL Server 2005, download the DTS Designer component and follow the steps above, and all will be fine. If you are porting to 64-bit SQL 2005, put your OCX components in \SysWOW64 instead of \System32 and it should work.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
-
Thanks, I'll give it a try.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply