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
>

DTS Job running way too long

Hello,
I have a DTS package that pulls data out of an Oracle database and
inserts it into a SQL Server database for reporting. The package
executes in about 2 seconds when I run it directly (right-click on the
package and 'execute'). But when I schedule the job to run, it runs
forever and never completes. Any tips or words or wisdom from the
experts out there?
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!check the accout runing your sql server to oracle!
running a job from your work station is different from running a job on
schedule.
one uses the security context of your workstation, the other uses the
security context of the box SQL is running on.
perhaps your server acct does not have rights to oracle? if there's no
timeout set for connection, it will run forever.
Steve Lin
MCDBA
"Rachael" <rachael_faber@.hotmail.com> wrote in message
news:OSk3CS0GEHA.2308@.tk2msftngp13.phx.gbl...
> Hello,
> I have a DTS package that pulls data out of an Oracle database and
> inserts it into a SQL Server database for reporting. The package
> executes in about 2 seconds when I run it directly (right-click on the
> package and 'execute'). But when I schedule the job to run, it runs
> forever and never completes. Any tips or words or wisdom from the
> experts out there?
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!