Hello,

I don't know much about DTS, so I have a question:

We have an SQL Server database where we have 18 DTS packages.
As part of our disaster recovery plan we need to install these. Is there a
way to do this by TSQL?

Thanks,
Ragnar

Hello Ragnar,

This article will give you some ideas

http://www.sqldts.com/default.aspx?204

Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com

Thank you Allan,

From what I can see, the answer is no.
I have not heard from the guy who created the CD which we will use to
recover, but I guess he used the dts backup to store the packages on the CD.
Reding the articles you referenced makes me think that restoring the dts
packages from the CD will have to be done manually.

Ragnar

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

Hello Ragnar,


You use a CD to recover what? Your DBs?

DTS packages if stored as local packages are located in MSDB so all you need
is a backup of MSDB and then restore it. If you want to move the packages
around on CD and not in a backup then you can export sysdtspackages from
MSDB to text file, copy to CD and then import them at the other end. This
can be automated because it is a simple copy of a text file.




Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com

Thank you Allan,

No, we don't back up our DB's to a CD, but the guy who created the DTS
packages may not be aware that they are stored in msdb so he backed them up
to a CD. I wasn't either until I saw something about it in the articles you
pointed me to. Some of them are quite complex, so I guess he wanted to have
copies, just in case.

We back up all our DB's, including master, model and msdb to other servers,
on a regular schedule, so that takes care of restoring the DTS packages
quickly.
I am responsible for putting together a disaster recovery plan, to make sure
we can recover within specified times from just about any conceivable
disaster, so I am looking for the fastest possible ways to recover.

Thanks for your help
Ragnar

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

Allan,

Where are the packages stored in msdb? I ran exec msdb..sp_enum_dtspackages
and it listed only one.
I have seen something about the Repository, but I have no idea what it is.

Ragnar

"Ragnar Midtskogen" <ragnar_ng (AT) newsgroups (DOT) com> wrote

Hello Ragnar,

have a look in sysdtspackages

Maybe the previous guy only actually saved them to storage files and the
CD is a copy of those packages???

Look on the filesyatem for anything with an extension of .dts


Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com

Hello Allan,

As Alice said: "This is getting curiouser and curiouser..."
There is one DTS item in the sysdtspackages and that is the one listed by
exec msdb..sp_enum_dtspackages, but that is not one of the packages listed
as required to operate the application.
There is a whole bunch of files with extension dts, those include those the
guy lists as being backed up as 'Structured Storage Files'
The packages must be in SQL Server somewhere, they are being used every
night, importing data from an Alpha4 database.
Can SQL Server use the dts packages in those files or do they have to be in
the msdb database?

Ragnar

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

Hello Ragnar,

They can be in files with no problems.

Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com

Thank you Allan,

But if they are in files, then restoring the msdb database would not restore
the DTS packages then?

Ragnar

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote