I manage 5 separate but similar processes supporting 5 different
websites. All of these involve an "aggregation" SQL Server dedicated
to processing daily/weekly/monthly batch files into various
non-additive aggregations (OLAP is thus ruled out). The processing is
coded into an aggregation DTS package in a series of ExecSQL tasks
which in the weekly case of the one weekly process churns out roughly
200GB of data.

About 100GB of this data is read from the aggregation server to a data
mart SQL server using a "Simple recovery model" database. This is done
with "publication" DTS package running a series of ExecSQL tasks
containing non-logged SELECT INTO's to bring data quickly from
aggregation server to data mart server (via linked server). For each
table brought over from aggregation server to the data mart server,
the SELECT INTO task is followed by a task to build indexes for that
table and finally a DBCC UPDATEUSAGE to make sure the indexes are in
proper shape.

Some time ago we implemented a DTS ActiveX (VBScript) task to automate
the execution of the publication process upon completion of the
aggregation process. Because there were other maintenance tasks
besides just starting the publication DTS package, an execute package
task alone is insufficient to do what we need to do. In 3 of the 5
processes which process smaller batches on a daily basis, this task
works flawlessly. We applied the same task to the weekly process and
for a few months we had flawless performance. However that is
currently not the case. I currently get Error -2147467259
(Unspecified) showing up on the msdb..sysDTSStepLog table. No
descriptions of this error show up in this table. Other than that, I
am getting no other error indications or error messages in the
server/application event logs that match the time of this failure. All
in all, there are just no clues to help me get at the cause of this
issue.

This ActiveX task is run from the aggregation server and executes the
publication DTS package on the datamart server by creating a
DTS.Package object in vbscript and then loading it and executing this
object. This, in turn, starts a DTS package in the data mart server
which reads from the aggregation server to SELECT INTO new data mart
tables. The failure point is intermittent and it has occurred at many
different points in the publication packages execution including both
SELECT INTO tasks and indexing tasks.

I'm just unable to assess why I get flawless performance on a smaller
daily version of this process, but have headaches with the larger
weekly version. Also I'm frustrated at the lack of clues to help me
diagnose this problem despite making extensive efforts to log and
error trap this process where ever possible. Can you help???