DSXchange

Hi All,

I would like to develop a job which checks for the datawarehouse connection and sees if its up and running before it triggers my job sequences. And, if the datawarehouse in not available I would like this job to wait for some time before it checks for the connection again and then triggers my job sequences. One way I can think of is to execute a sql statement like
"select sysdate from dual"
and see if the DW is up or not. But I am scratching my head on how to make it wait(sleep) for some time before it tries to check the connection again. It would be great if anyone could suggest me some ideas or direction and also let me know if this is a good practise to do.

Thanks
Kris[/code][/quote]

Kris

Seach on this forum for Wait for File Activity or poll. This has been discussed before

I did search on it, but my requirements here are quite different just waiting for file wont do,sorry for not being specific earlier. I would like to connect to Database for every min or so (loop activity) until i get the connection to oracle I want to keep tryin say like atleast 5 times or (few min).

P.S my server is Windows

If you're on 7.5x2 you can use StartLoop and EndLoop activities in your job sequence.

Ray,

I can use StartLoop and EndLoop.But, if the DW is down I need my job to wait for sometime and check for it again. For that, I am assuming I need to write a routine for a wait time. Am I thinking right. To be more clear, when the loop starts and if my DW is up the next jobs in the sequence run. But if my DW is down, then the job which checks for DW connection fails and the output should go to a routine or a function where it needs to wait for say like 10 min before starting the loop again. I didnt find any routine which can do this. Is there one or do we need to write our own routine. If so, can I get some guidance in writing the routine.

Thanks
Kris.

Use the Wait For File stage set to look for a non-existant file and then to give up after your desired 'sleep' time.

A job to check the connections can be made by the command you had mentioned

Thanks Craig. That works, but only if I dont use the ExceptionHandler. I am assuming that it works that way because I am handling the errors by giving a Failed trigger.

Kris.

Yes, because the Wait For File stage has to 'fail' when it doesn't find a file it can trigger the Exception Handler. A couple of things you can do about that -

1) Try using either an 'Unconditional' trigger instead of a Failed one or two triggers, an Ok and an Otherwise so that it thinks you are handling any exceptions so it doesn't need to.

2) Replace the Wait For file with a Routine Activity that calls a custom Sleep X Seconds routine. Minimum code would be all of:
Of course, you may want to make it... smarter... than that.

Thanks Craig. That helped.

Kris.