Dts package to run every 10 seconds

Does anyone know if you can shedule a dts package to run every 10 seconds ?
I can see that you can create a job but this only has the option for the earliest run set to 1 minute.
Any advice
IanYou would probably have to write a batch/script file that runs with some form of a loop that will sleep for 10 seconds, and then exec the packaged via DTSRun within that while loop.

Question though, why do you need to run this every 10 seconds ?

Here is a simple batch file that could do it, but you would really need to add some error checking, being it can run endlessly.

run.cmd file
@.ECHO OFF
:begin
sleep 10
echo DTSRun command goes here
GOTO END

:END
goto BEGIN|||Nice batch file.

You could also do this by having your job (scheduled once per minute) call a stored procedure that executes the DTS package six times in 10 second intervals. Plus, I wouldn't be surprised if there was a way you could rig this up in the DTS package yourself, either using coding or by simply repeating the process six times in a series of linked steps.|||Nice batch file.

You could also do this by having your job (scheduled once per minute) call a stored procedure that executes the DTS package six times in 10 second intervals. Plus, I wouldn't be surprised if there was a way you could rig this up in the DTS package yourself, either using coding or by simply repeating the process six times in a series of linked steps.

This way seems way safer instead of using the .cmd file.|||Another alternative is that you could run your
DTS package as step 1,3,5,7,9, and 11 in the scheduled job,
with step 2,4,6,8, and 10 being a T-SQL command:

WAITFOR DELAY '00:00:10'

See BoL ...

WAITFOR
{
DELAY 'time_to_pass'
| TIME 'time_to_execute'
| ( receive_statement ) [ , TIMEOUT timeout ]
}sqlsql

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 Runs Forever

I can manually run a DTS package from SQL Server 2000 where the data
source is an Oracle database. Run it manually and it completes in a
few seconds. When I try to run it as a job it runs forever.
I read other posts suggesting that the SQLAgentCmdExec needs to be a
user on the Oracle server (Sun box). Why is that if the ODBC
connection already authenticates the connection? This is what is
confusing IAW Q269074.
Thanks for any suggestions.
RBollinger
The most common causes of a package running ok manually but failing as a job
are either security or environment related. When you run the package
manually, are you doing so on your PC or on the SQL Server (directly or via
Remote Desktop)? The Windows security context isn't usually an issue for
the Oracle connection, although it might be a factor for other resources
need by the package.
Hope this helps.
Dan Guzman
SQL Server MVP
"robboll" <robboll@.hotmail.com> wrote in message
news:1159215526.092875.48620@.i3g2000cwc.googlegrou ps.com...
>I can manually run a DTS package from SQL Server 2000 where the data
> source is an Oracle database. Run it manually and it completes in a
> few seconds. When I try to run it as a job it runs forever.
> I read other posts suggesting that the SQLAgentCmdExec needs to be a
> user on the Oracle server (Sun box). Why is that if the ODBC
> connection already authenticates the connection? This is what is
> confusing IAW Q269074.
> Thanks for any suggestions.
> RBollinger
>
|||I found the solution:
To correct the problem I installed SQL Server Service Pack 4. That's
it.
robboll wrote:
> I can manually run a DTS package from SQL Server 2000 where the data
> source is an Oracle database. Run it manually and it completes in a
> few seconds. When I try to run it as a job it runs forever.
> I read other posts suggesting that the SQLAgentCmdExec needs to be a
> user on the Oracle server (Sun box). Why is that if the ODBC
> connection already authenticates the connection? This is what is
> confusing IAW Q269074.
> Thanks for any suggestions.
> RBollinger

DTS Job Runs Forever

I can manually run a DTS package from SQL Server 2000 where the data
source is an Oracle database. Run it manually and it completes in a
few seconds. When I try to run it as a job it runs forever.
I read other posts suggesting that the SQLAgentCmdExec needs to be a
user on the Oracle server (Sun box). Why is that if the ODBC
connection already authenticates the connection? This is what is
confusing IAW Q269074.
Thanks for any suggestions.
RBollingerThe most common causes of a package running ok manually but failing as a job
are either security or environment related. When you run the package
manually, are you doing so on your PC or on the SQL Server (directly or via
Remote Desktop)? The Windows security context isn't usually an issue for
the Oracle connection, although it might be a factor for other resources
need by the package.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"robboll" <robboll@.hotmail.com> wrote in message
news:1159215526.092875.48620@.i3g2000cwc.googlegroups.com...
>I can manually run a DTS package from SQL Server 2000 where the data
> source is an Oracle database. Run it manually and it completes in a
> few seconds. When I try to run it as a job it runs forever.
> I read other posts suggesting that the SQLAgentCmdExec needs to be a
> user on the Oracle server (Sun box). Why is that if the ODBC
> connection already authenticates the connection? This is what is
> confusing IAW Q269074.
> Thanks for any suggestions.
> RBollinger
>|||I found the solution:
To correct the problem I installed SQL Server Service Pack 4. That's
it.
robboll wrote:
> I can manually run a DTS package from SQL Server 2000 where the data
> source is an Oracle database. Run it manually and it completes in a
> few seconds. When I try to run it as a job it runs forever.
> I read other posts suggesting that the SQLAgentCmdExec needs to be a
> user on the Oracle server (Sun box). Why is that if the ODBC
> connection already authenticates the connection? This is what is
> confusing IAW Q269074.
> Thanks for any suggestions.
> RBollinger

DTS Job Runs Forever

I can manually run a DTS package from SQL Server 2000 where the data
source is an Oracle database. Run it manually and it completes in a
few seconds. When I try to run it as a job it runs forever.
I read other posts suggesting that the SQLAgentCmdExec needs to be a
user on the Oracle server (Sun box). Why is that if the ODBC
connection already authenticates the connection? This is what is
confusing IAW Q269074.
Thanks for any suggestions.
RBollingerThe most common causes of a package running ok manually but failing as a job
are either security or environment related. When you run the package
manually, are you doing so on your PC or on the SQL Server (directly or via
Remote Desktop)? The Windows security context isn't usually an issue for
the Oracle connection, although it might be a factor for other resources
need by the package.
Hope this helps.
Dan Guzman
SQL Server MVP
"robboll" <robboll@.hotmail.com> wrote in message
news:1159215526.092875.48620@.i3g2000cwc.googlegroups.com...
>I can manually run a DTS package from SQL Server 2000 where the data
> source is an Oracle database. Run it manually and it completes in a
> few seconds. When I try to run it as a job it runs forever.
> I read other posts suggesting that the SQLAgentCmdExec needs to be a
> user on the Oracle server (Sun box). Why is that if the ODBC
> connection already authenticates the connection? This is what is
> confusing IAW Q269074.
> Thanks for any suggestions.
> RBollinger
>|||I found the solution:
To correct the problem I installed SQL Server Service Pack 4. That's
it.
robboll wrote:
> I can manually run a DTS package from SQL Server 2000 where the data
> source is an Oracle database. Run it manually and it completes in a
> few seconds. When I try to run it as a job it runs forever.
> I read other posts suggesting that the SQLAgentCmdExec needs to be a
> user on the Oracle server (Sun box). Why is that if the ODBC
> connection already authenticates the connection? This is what is
> confusing IAW Q269074.
> Thanks for any suggestions.
> RBollinger