Restoring the MSDB database will restore all the DTS packages, SQL Agent Jobs, Backup history, etc...

Take a look at DTSBackup2000 over at http://www.sqldts.com

They also list a couple of other methods...

Try the Development Studio

Another approach to calling a sub-package is to use an ExecuteProcess task and call DTSRUN.EXE

For a basic overview of how the system is running and at the right price ( Free  ), try

http://www.idera.com/Products/SQLcheck/Default.aspx

I've also used Spotlight...

Specify an output file on the advanced tab of the job step.

As you're already using OPENROWSET, take a look at OPENDATASOURCE.

You could also implement this totally using Anatol's scheduled job suggestion and forget using a DTS package.

A scheduled job has the basic looping and/or retry mechanism that's needed. eg: On Failure...

Given that you don't have a maintenance plan, I'd suggest doing a re-index or update usage. The statistics used by the query optimizer are probably way out of date.

Then I'd...

Probably a more supported method would be to create a seperate SQL login that has the necessary privelages to create and edit the DTS packages. Then people changing the DTS...

xp_smtp_sendmail from http://www.sqldev.net has a parameter that allows you to use a text file for the body.

" ...My solution is just a process that creates a flatfile so the assessment of a correctly designed database has no play here, my goal is to create an odd...

In the example I provided there is no hand coding...

Without a loop ...

set nocount on

Create table #tmp (
 AcctID int
 , Zip varchar(10)
 , Phone varchar(20)
)

insert into #tmp values (11212, 23332, '333-444-5555')
insert into #tmp values (23223, 34434, '444-333-5545')
insert into...

Personally, I wouldn't be "forgetting" everything else.

You're basically running a cursor within a cursor, and I'm sure I'm not the only one that would point out the possible performance issues...

I've never failed a software project, but I've had a few fail me