DTS just keeps on running

I have a DTS that pulls data from an Informix database, scheduled in a job
to run every 10 minutes. The DTS takes less than 30 seconds noramally.
Occasionally, the JOB will execute endlessly (hours/days). There does not
appear to be a way to tell a Job to terminate if a certain amount of time
elapses, and simply retry when the next 10 minute interval occurs.
I see several options, but these all seem like I'm making what should be a
very simple task into a complicated one ...
1) Have a table the tracks the last time the job executed successfully.
Create a VBScript DTS task that checks every 15 minutes or so to confirm
that the date has in fact incremented. if not, programatically kill the
job, and restart it.
2) Create a windows service using .NET to do the same thing. A separate
thread or similar could be used to confirm that the process has actually
finished in a reasonable amount of time.
However, I'm hoping there is a quicker option ... suggestions? Final
assumptions:
A) The DTS is read-only. It does not require locks.
B) There are no informix error messages returned if I stop the job. The job
then indicates that it was a "user requested cancel" or similar.
Thanks in advance.
MarkUse sp_who2 to determine if there are any sessions blocked.
"Mark" <Mark@.nowhere.com> wrote in message
news:eNNS3i8WFHA.1040@.TK2MSFTNGP10.phx.gbl...
> I have a DTS that pulls data from an Informix database, scheduled in a job
> to run every 10 minutes. The DTS takes less than 30 seconds noramally.
> Occasionally, the JOB will execute endlessly (hours/days). There does not
> appear to be a way to tell a Job to terminate if a certain amount of time
> elapses, and simply retry when the next 10 minute interval occurs.
> I see several options, but these all seem like I'm making what should be a
> very simple task into a complicated one ...
> 1) Have a table the tracks the last time the job executed successfully.
> Create a VBScript DTS task that checks every 15 minutes or so to confirm
> that the date has in fact incremented. if not, programatically kill the
> job, and restart it.
> 2) Create a windows service using .NET to do the same thing. A separate
> thread or similar could be used to confirm that the process has actually
> finished in a reasonable amount of time.
> However, I'm hoping there is a quicker option ... suggestions? Final
> assumptions:
> A) The DTS is read-only. It does not require locks.
> B) There are no informix error messages returned if I stop the job. The
job
> then indicates that it was a "user requested cancel" or similar.
> Thanks in advance.
> Mark
>|||This DTS pulls from an INFORMIX database ... sp_who2 doesn't fit the
situation. Moreover, I've confirmed that there are no locks on the informix
table. Finally - this DTS is read-only, so no locks are required.
Other recommendations? Thanks in advance.
Mark
"JT" <someone@.microsoft.com> wrote in message
news:u$HzP98WFHA.2520@.TK2MSFTNGP09.phx.gbl...
> Use sp_who2 to determine if there are any sessions blocked.
> "Mark" <Mark@.nowhere.com> wrote in message
> news:eNNS3i8WFHA.1040@.TK2MSFTNGP10.phx.gbl...
> job
>|||Even a select.. can attempt to acquire locks depending on the transaction
isolation level. If I'm querying reports from OLTP database, I set isolation
level to read uncommitted. I don't know that much about Informix, but is
there an eqivolent to sp_who that can tell you what the process on that
server is doing?
"Mark" <Mark@.nowhere.com> wrote in message
news:eZB%23UM9WFHA.3620@.TK2MSFTNGP09.phx.gbl...
> This DTS pulls from an INFORMIX database ... sp_who2 doesn't fit the
> situation. Moreover, I've confirmed that there are no locks on the
informix
> table. Finally - this DTS is read-only, so no locks are required.
> Other recommendations? Thanks in advance.
> Mark
>
> "JT" <someone@.microsoft.com> wrote in message
> news:u$HzP98WFHA.2520@.TK2MSFTNGP09.phx.gbl...
time
be
confirm
the
separate
actually
The
>|||That's fair, but let's assume I can't fix the lock on Informix as it's not
mine ... my goal here is to have my DTS/JOB be smart enough to stop after
some fixed amount of time, like 30 seconds. Is there a way to do this aside
from my original ideas below? Basically, based on our business rules, I
have to assume that the Informix database is a black box.
Thanks in advance.
Mark
"JT" <someone@.microsoft.com> wrote in message
news:uo1i8m9WFHA.3876@.tk2msftngp13.phx.gbl...
> Even a select.. can attempt to acquire locks depending on the transaction
> isolation level. If I'm querying reports from OLTP database, I set
> isolation
> level to read uncommitted. I don't know that much about Informix, but is
> there an eqivolent to sp_who that can tell you what the process on that
> server is doing?
> "Mark" <Mark@.nowhere.com> wrote in message
> news:eZB%23UM9WFHA.3620@.TK2MSFTNGP09.phx.gbl...
> informix
> time
> be
> confirm
> the
> separate
> actually
> The
>|||SQL Server supports the following, and perhaps Informix has something
equivilent:
SET QUERY_GOVERNOR_COST_LIMIT value
Also, I beleieve there is a property (CommandTimeout or something) of the
ADO connection object that will cap the duration of a query. In DTS, the
connection task has an Advanced.. button where some (but perhaps not all)
options supported by the database driver can be configured.
"Mark" <Mark@.nowhere.com> wrote in message
news:%23m4yA69WFHA.2700@.TK2MSFTNGP12.phx.gbl...
> That's fair, but let's assume I can't fix the lock on Informix as it's not
> mine ... my goal here is to have my DTS/JOB be smart enough to stop after
> some fixed amount of time, like 30 seconds. Is there a way to do this
aside
> from my original ideas below? Basically, based on our business rules, I
> have to assume that the Informix database is a black box.
> Thanks in advance.
> Mark
>
> "JT" <someone@.microsoft.com> wrote in message
> news:uo1i8m9WFHA.3876@.tk2msftngp13.phx.gbl...
transaction
a
does
should
Final
>