March 3, 2011 at 4:26 pm
Hello All!
I have a package setup to run everyday in the AM. Is there a way to kick off the package based on an openquery to a Oracle server when that query result returns the value of '1'?
Can SSIS run thru a loop of some sort that runs the query every 15 minutes until the condition is met then in turn executing the job?
I know this is probably a resource hog and possibly a stored proc would be a better solution, but I was just wondering if it could be done within SSIS.
TIA!
March 4, 2011 at 1:39 am
I would run a SQL Server Agent job every 15 minutes. This job will execute a stored procedure.
The stored procedure runs the openquery and checks the result. If it is 1, it will kick off the SSIS package (either by using the dtexec utility, or by starting another job that contains the SSIS package).
edit: to answer your question if it can be done in SSIS alone: the looping part is possible, if you let the SSIS package run forever. The condition can be checked by executing the query in an Execute SQL Task, storing the resultset in a variable. Then you can set up a conditional precedence constraint that checks the value of the variable.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply