Hi !
I have a load job that should be triggered when a row appears on the control Table.
I am trying to write a control job which will call the load job.
I am stuck at this place and I donot know how to go about this. The control job should monitor the control table (for the presence of row) every 15 minutes (for a maximum of 10 hrs). If the row is present then the LoadJob should be called.. If the row is not present.. The job should sleep for 15 minutes and then repeat the process again.
I am getting stuck at
1.How to find out if the row is present or not and inform the job sequence to proceed with the load job.
2. How to end the looping activity once the load job is triggered.
Any thoughts?
- Nagesh
Job Sequencing
Moderators: chulett, rschirm, roy
How comfortable are you with writing your own Job Control? This would be pretty straight-forward if you are but you may be able to do something in the GUI with the new Start/End Loop constructs as well.
You could create a simple job that sources that row from the table in question. It doesn't really need to do much with it, perhaps even just write it to a sequential file somewhere. Then a custom routine that either does a DSGetLinkInfo to check the row count in the job (0? 1?) or one that checks the size of the file (0? >0?) would tell you if there was something on the end of your line or if you needed to go back and fish some more.
Sleep 900 seconds (15 minutes) via another Routine inside the loop. Exit the loop if the total runtime exceeds 10 hours or you found the row. When you do exit, you can then either run your load job or trigger an exception email (if you like) depending on which exit you took.
There are examples of the Loop stages in the docs. I haven't played with them much yet, preferring to hand code stuff like that, but I'm sure you would be able to do something like the above. There can be more to it, like building a way to exit early if you want the job to stop, but this should get you started. I hope.
You could create a simple job that sources that row from the table in question. It doesn't really need to do much with it, perhaps even just write it to a sequential file somewhere. Then a custom routine that either does a DSGetLinkInfo to check the row count in the job (0? 1?) or one that checks the size of the file (0? >0?) would tell you if there was something on the end of your line or if you needed to go back and fish some more.
Sleep 900 seconds (15 minutes) via another Routine inside the loop. Exit the loop if the total runtime exceeds 10 hours or you found the row. When you do exit, you can then either run your load job or trigger an exception email (if you like) depending on which exit you took.
There are examples of the Loop stages in the docs. I haven't played with them much yet, preferring to hand code stuff like that, but I'm sure you would be able to do something like the above. There can be more to it, like building a way to exit early if you want the job to stop, but this should get you started. I hope.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
You would write a 'generic' routine that takes the three required bits of information as arguments. Check the syntax of the command - you'll need a handle to the job to check and the name of the link and the transformer it comes from.
Your routine would then take the jobname you passed in and try to 'attach' to it. If successful, take the resulting job handle and call the DSGetLinkInfo function. Pass back the resulting count or any error encountered during the process. Don't forget to 'detach' before you exit the routine.
Keep in mind the fact that, if the Sequence job is automatically handling 'activities that fail', a routine that returns a non-zero result is considered to have failed. Handle that with the appropriate error triggers as documented in the online help so that it thinks *you* are handling any errors from the routine so it doesn't have to.
Your routine would then take the jobname you passed in and try to 'attach' to it. If successful, take the resulting job handle and call the DSGetLinkInfo function. Pass back the resulting count or any error encountered during the process. Don't forget to 'detach' before you exit the routine.
Keep in mind the fact that, if the Sequence job is automatically handling 'activities that fail', a routine that returns a non-zero result is considered to have failed. Handle that with the appropriate error triggers as documented in the online help so that it thinks *you* are handling any errors from the routine so it doesn't have to.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
HI Craig,
Many thanx for the tip.
I have designed the job in a sligthly different way..
1. Start with the Start Loop Activity.
2. Call the Job Which will Read from the source table and create a file called "triggerfile" in the predefined directory, if the source table has a record with Null extract date.
3. Call the Wait for the Job Activity and check for the presence of the file.
3.1.1 If the file is not present then call a routine to sleep
3.1.2 End the loop and go to Step 1
3.2.1 If the file is present then Delete that file
3.2.2 Call the load job
It works..
Thanx again
Nagesh
Many thanx for the tip.
I have designed the job in a sligthly different way..
1. Start with the Start Loop Activity.
2. Call the Job Which will Read from the source table and create a file called "triggerfile" in the predefined directory, if the source table has a record with Null extract date.
3. Call the Wait for the Job Activity and check for the presence of the file.
3.1.1 If the file is not present then call a routine to sleep
3.1.2 End the loop and go to Step 1
3.2.1 If the file is present then Delete that file
3.2.2 Call the load job
It works..
Thanx again
Nagesh
-
I_Server_Whale
- Premium Member
- Posts: 1255
- Joined: Wed Feb 02, 2005 11:54 am
- Location: United States of America
Out of curiousity, how are you keeping it from creating the file regardless of the outcome of the source query? Just having the Sequential File stage in a job will create an empty file when it runs...srinagesh wrote:2. Call the Job Which will Read from the source table and create a file called "triggerfile" in the predefined directory, if the source table has a record with Null extract date.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
One way of acheiving it, to process out of the job. May be based on the status of the previous job, a Execute command activity can be called to touch a file. (The previous job should read the file and should changed its state either aborted or Finished ok). Or with After job subroutine to call a shell which inturns check the outcome of the job and touch a file.chulett wrote:Out of curiousity, how are you keeping it from creating the file regardless of the outcome of the source query? Just having the Sequential File stage in a job will create an empty file when it runs...srinagesh wrote:2. Call the Job Which will Read from the source table and create a file called "triggerfile" in the predefined directory, if the source table has a record with Null extract date.
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
Source -----> Transformation -----> Folder StageOut of curiousity, how are you keeping it from creating the file regardless of the outcome of the source query? Just having the Sequential File stage in a job will create an empty file when it runs...
Source is Oracle. The Source will have only one column called "Count"
The SQL query for the source is
- Select count(*) as count from table_name where <<condition>>
So the output of this job will either be a "TriggerFile" or a "NoTriggerFile" (Depending on whether there are any rows in source with the matching condition or not)
My Job sequence has a FileWait on the TriggerFile.
-Nagesh