Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2000 Forums  Import/Export (DTS) and Replication (2000)  DTS Package gets stuck on Executing status

Author  Topic  gcrout Starting Member 1 Post Posted - 2005-05-17 : 11:25:29 I have a DTS package that moves data from an accounting package into a SQL Server DB hourly. Occaisionally, the package gets stuck and the job remains in an "executing" status until it is manually stopped. Because I wanted to setup e-mail notifications upon this type of failure, and because Exchange or another MAPI mail system is not present on this machine, I setup a stored procedure to send e-mails via the CDOSYS object model. The job has two steps. The first executes the DTS package. The second sends the alert by executing the stored procedure via transact SQL statement. I have the first job set to quit with success so that the failure would never be sent if the DTS package works. I have it set to proceed to the next step upon failure so that the alert would be sent. So the problem, as mentioned above, is that when it doesn't work the job is just remainining in an executing status and the first step is never failing (and the the notification never gets sent.) Is there someway that I can have the DTS package or the first job step time out after a certain amount of time (say 10 minutes) so that a failure occurs? Thanks very much. gcrout