SQL ate my DTS package 1

[Kubilus1]

Hello,

I have a DTS package used for importing data from an Access database into SQL Server 7.0 (sp3), and for running several scripts on the data. Last week it was working fine, but when I got to work this morning, SQL can't find it.

The package still shows up under Data Transformation Services/Local Packages, but when I choose Execute Package I get an error stating: "Error Description: %1 could not be found."
Choosing Design package causes the same error. Version History will show all the versions of the package, but when
edit is chosen, I get the same error again.

We do nightly backups of our SQL databases to tape through ArcServe's SQL backup agent. Is there a way I can restore just a DTS package from SQL without having to do something as drastic as restoring the entire master database from last week? Perhaps I should be putting this post under the ArcServe backup forum?

I have spent quite a few hours on the DTS package, and do not want to have to repeat all that work. I would really appreciate any suggestions.

Matt

 

[RickCole]

Unfortunately you can not restore an individual DTS package from your backups. However the DTS packages are held on the msdb database and not master. Therefore if you haven't had too much activity on your msdb database you could restore that.

The reason for the error although I am going back a while is as follows:

The problem is caused by someone from a client machine using Enterprise manager to edit or at least open and save the DTS package. The client machine in question has an earlier version of the SQL Server service pack installed compared to the PC from which you originally created the package.

To get round the problem edit the DTS package from the offending client PC (if you can find which one) and save it to a file rather then SQL Server. Then you can retrieve it from the file using your own PC and DTS editor and save it to SQL server. You should then be able to use as normal.

Then update the offending PC with the latest service pack.

Hope I've remembered all that correctly but post again if either the above don't work as I know I've got around this problem myself.

Rick.

 

[Kubilus1]

There are only a couple machines that would have accessed the package. I've tried opening it on all of them and I get the same (or similar) error message.

Restoring the msdb database sounds promising. What kind of issues should I watch out for when restoring the msdb. Our SQL server contains several databases used in production, and I don't want to interupt service.

Matt

 

[tlbroadbent]

You can restore msdb as another database such as msdbRestore and then transfer the package from msdbRestore to msdb. An article at SQLDTS.com explains how to transfer the package.

Transferring DTS Packages

http://www.sqldts.com/main.asp?nav=1,6,204,0

Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums. NOTE: Reference to the FAQ is not directed at any individual.

 

[RickCole]

Kubilus1 try the following:

1) Stop the SQL Server Agent service.

2) Using current activity in Enterprise manager ensure no has a connection to the msdb database.

3) Backup the current msdb to restore later.

4) Restore the msdb database from a backup where you know the DTS package was accessible.

5) After the restore you should be able to edit the DTS package and save it to file.

6) Restore the msdb database from the backup you have taken in step 3.

7) Re-start SQL Server Agent.

8) Retrieve DTS package from file taken in step 5 nd save to SQL server.

Hope this gets you back to where you were. By the way this will not impact any other service or production database you have on your server whilst you do this.

The only thing you have to watch out for is that whilst the SQL Server agent is stopped no scheduled jobs you have configured to run will start.

Rick.

 

[RickCole]

Have just seen Terry's post and it's a neater solution so use that method.

Hopefully my post still dispels any fears about restoring the msdb database should the need arise in the future.

Rick.

 

[Kubilus1]

Both those suggestions are really great, but I'm having a few problems.

I should add, we are not backing up databases to a BAK file and letting Arcserve pick-up those during tape backup. Instead we are using an Arcserve SQL database agent which communicates directly with SQL server to retrieve the data. Arcserve will not let me restore a database to a different name. (Or if it is possible I can't figure it out.) Therefore I can't use Terry's method.

So I tried your way, Rick, and Arcserve told me that the database was restored successfully. However I get the same error when I try to access the DTS package! Is there a way that I can check in SQL server, whether a particular database was restored on a particular date? Any other idea why it still wouldn't run?

Thanks a lot,
Matt

 

[RickCole]

There is a table in SQL Server called restorehistory held in the msdb database which holds information on all databases restored.

Select * from msdb..restorehistory

Might also be worth trying to transfer an earlier version of the package using the method described at

http://www.sqldts.com/main.asp?nav=1,6,204,0

.You can do this on your existing server but create the package you are transferring to with a different name. Then try accessing the newly transferred package.

Rick.

 

[Kubilus1]

Okay, that query shows that it is restored. Package still won't work. I appreciate the help, but I can't spend any more time on this. Better get started rebuilding that sucker. Oh well.

Matt

 

[Kubilus1]

Wait! Now when I try to import data using the import data wizard, DTS won't run at all. It works through most of the wizard until you hit finish, then EM just freezes. Task manager does not show EM as (Not Responding). What's going on here?

Matt

 

[RickCole]

Does sound like you do have a problem with the DTS service. I've not come across this freezing problem before but if it was me I would try the following:

Install the latest SQL Server service pack. I do know that DTS had initially a lot of problems and the service packs have been correcting a lot of these.

If the latest service pack is already installed then re-install it as this might kick start DTS back into action.

If the service pack install doesn't cure the problem then I would then re-build the master database. Not something I would wish to do but useful as a practice exercise should you ever have to do it in an emergency.

And finally if the rebuild doesn't work then my next course of action would be a complete re-install of SQL Server. And restore your databases from your backups.

I know some of the above options are not what you would prefer to do but it just might be the quickest option.

Rick.

 

[Kubilus1]

Thank you very much Rick, it turned out to be a service pack problem afterall. After reinstalling sp3 on my developement machine, DTS works. In addition that specific DTS package works as well!!

There is only one remaining problem. Now whenever I start enterprise manager, If get an error stating:

"DCOM has not been installed on this operating system and absence is known to cause incompatibility issues with this application. Please contact your system administrator."

This message appears before the EM window actually loads. Clicking 'Yes' allows me to use EM and the DTS part works fine, which is great. However, this message is a bit worrysome. Any ideas what DCOM is or how I would install it?

Thanks again,
Matt

 

[RickCole]

What operating system and service pack are you running on your server???

 

[Kubilus1]

I'm running Windows NT4 (sp6) on the server, SQL server is running sp3.

On my developement machine I'm running windows 98 with SQL server 7 desktop edition.

 

[RickCole]

DCOM is generaly installed along with the operating system. AS to how it's got messed up I'm not sure but maybe re-installing DCOM might cure the problem. Have a look at the following links, which might point you in the right direction.

http://support.microsoft.com/support/kb/articles/Q196/2/08.ASP

http://support.microsoft.com/support/kb/articles/Q198/6/00.ASP

If it's on the NT machine try re-installing the NT service pack.

Rick.