Saving DTS Packages

[Si64]

I need to create DTS packages for importing data from UNIX to SQL Server 2000. We are using a proprietory ODBC bridge between the two which appears to be working OK - validated by the fact that my DTS packages run properly and import data into tables as there supposed to. I can't, however, save any DTS packages. When I try to save anything, either from the DTS design package window or by checking the 'Save DTS Package' box on the 'DTS Import/Export Wizard', I get the following error:


Error Source: Microsoft OLE DB Provider for SQL Server
Error Description: Invalid column name 'packagetype'


Any pointers anyone?

 

[yksvaan]

Try saving it as a (structured storage) file (.dts file) from dts designer. Does that work?

Saving to sql server the package goes into msdb database, you should have necessary permissions to that database. Can you open an other old package from local packages and save it with a new name ?

Cheers


[blue]Backup system is as good as the latest recovery[/blue]

 

[Si64]

Thanks for your help yksvaan. I can save the script as a .dts file without any trouble whatsoever.

Unfortunately, as this is a new server build, there aren't any other packages so I can't verify whether I can open any existing files.

Sorry, not much help really.

 

[Si64]

Eeek. I've just re-read my reply and it's a bit ambiguous.
When I said 'Sorry, not much help really' I meant my reply to yksvaan was not much help, not yksvaan's reply to my initial message.

Sorry for the confusion.

Simon

 

[Si64]

It turns out that one of the dts tables was missing. As this was a new server build, we have been able to re-install SQL 2000 - all's now well.

Thanks to all those who tried to help.

Simon

 

[mrdenny]

If this happens in the future all you should need to do is rebuild the msdb database. On the SQL CD in the install folder you'll find a file named instmsdb.sql. Simply run this and it will fix your msdb database.

If you know that your missing only a couple of tables you could find the correct bit of code and run that to correct just the missing tables.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[Si64]

Thanks Denny. I'll store this info in my personal knowledge base - not that that's too reliable at my age.

Simon