Sqler's
I am using SQL 7 and have been for about 3 years. DTS is
common practice in what I do. I have experienced an
anomoly with DTS. I have a DTS job that runs M-F for the
past 3 years. It runs a stored procedure that inserts data
into about 12 tables. The stored procedure is wrapped with
BEGIN and COMMIT transaction statements. The stored
procedure is executed via ActiveX object in the DTS job.
It used ADO to make the connection and executes the stored
procedure. The stored procedure executes perfectly with no
rollback or errors. On very rare occasion, after the DTS
job is done, the loaded data is missing. It is as though
the DTS job is running on its own transaction set and for
some reason rolls the data out after the stored procedure
runs. Any ideas. I am on service pack 3 with SQL.

Hi MG,
Try enabling a log to the package (Package Properties) to check what is going on.
In the properties, you could also have the package fail with th first error or configure transactions for the package.
For the Transform Task, you can also set the properties of error count and commit.
HTH,


----- MG wrote: -----

Sqler's
I am using SQL 7 and have been for about 3 years. DTS is
common practice in what I do. I have experienced an
anomoly with DTS. I have a DTS job that runs M-F for the
past 3 years. It runs a stored procedure that inserts data
into about 12 tables. The stored procedure is wrapped with
BEGIN and COMMIT transaction statements. The stored
procedure is executed via ActiveX object in the DTS job.
It used ADO to make the connection and executes the stored
procedure. The stored procedure executes perfectly with no
rollback or errors. On very rare occasion, after the DTS
job is done, the loaded data is missing. It is as though
the DTS job is running on its own transaction set and for
some reason rolls the data out after the stored procedure
runs. Any ideas. I am on service pack 3 with SQL.

HTH, Thanks for the thoughts. We have done all of that but
DTS does not really give you enough information in the log
to point to errors. Any other ideas would be appreciated.
Thanks
MG
package.