DSXchange

Hi all,

I have two job activities in a job sequence. The first job activity stages the data from the source after doing some transformations in a sequential file and the second job activity loads the data into the target table. I am collecting my reject rows in the first job activity. I would like my second job activity to run even if I have any reject rows in the first job, but fail if the first job fails. I am getting lost while declaring the triggers for the job activities. It would be great if anyone could guide me in the right direction. I have already searched but couldnt hit on any right topic

Thanks
Kris

I have done it by putting another trigger with expression type warning. Was trying to explore if there was a way to do it with two triggers from the first job, but now I have to do it with 3 triggers- one each for OK, Warning and Failed.


Kris

Why do you need three triggers? There are different ways to solve this, one way involves two triggers, another a compound trigger.

Use an 'Ok' and a 'Warning' trigger and run them to a Sequencer stage set to 'Any', then link the Sequencer to the second job.

Use one trigger with a Custom condition and code both conditions into it:
If you are running 7.5.x you can use the Exception Handler if you want to do something other than simply stop the Sequence when a job aborts.

Yeah...Cudnt get tht idea.Thanks. The first way looks simpler though.At this moment I want my jobs to run even if I have rejected rows as my jobs are not dependent. I still need to look into the Exception Handler. The only need I had so far with it is to abort my Master sequence if any of my jobs fail.

Thanks
Kris

There is another type of trigger that could be handy: The "Otherwise" trigger.
Instead of the 3 triggers,

You could run a "Failed" and an "Otherwise" trigger. from JobActivity1.

IHTH,

Well... keep in mind the fact that 'Otherwise' triggers catch anything that didn't go down another link. When you pair it up with a 'Failed' trigger, don't assume that the only thing it will catch are 'Ok' and 'Warning' events.

Any other result will go down the Otherwise link - included things like Crashed jobs.

That crashes th otherwise option..
Thanks Craig,

In the past, I would combine it with an 'Ok' trigger such that a job that ran ok would continue on and anything else - warnings, aborted, crashed, anything - would go down the 'Otherwise' link. Still perfectly good in a situation like that.

However, with the new Checkpointing and the ability to 'automatically handle' activities that have failed, I've dropped back to typically only having an 'Ok' trigger and letting the Sequence job itself (or the Exception Handler) do all the other work.

So, In my case, If I just have an OK trigger and an Exception Handler in the job, with check point and automatically handle options checked, will I be able to achieve what I wanted i.e. my second job runs even if I have rejected rows in the first job.


Kris

Depends on how you are handling the rejects. If having rejects means the job finishes with warnings, then no - you can't take that route.

You can do the checkpointing and exception handling but you'll still need either two triggers or a compound trigger so that either 'Ok' and 'Warning' results go forward from this job.

Craig,

Yeah, I realised that I cant take that route because I want my job to run even if it has rejects. But, with that I also need a notification if my job fails or has any warnings. I tried using Exception Handler with Utility Warning to Log but seems like I missed some thing. And if I keep a notification activity at the end of my second job it returns a mail with status ok. I can attach my reject file here in the notification activity but that ways I will have to check my mail everytime. Is there a way around where only I get a notification only if my job fails or has any warning. Thanks for your inputs.

Kris

You should be able to attach your Notification Activity for reporting success to your second job with an 'Ok' trigger. The Exception Handler will need to report out any failures in the Sequence.

Now it gets a little more complicated if you want the Job2 to run even if Job1 had warnings, but you want to email out the rejects from Job1 as well. Off the top of my head, you'll probably need a routine to check the size of your reject file or the number of records that went down the reject link in Job1 and then only send an email (with the attachment) if that routine says it found it. A custom trigger from the Routine to the reject Notification stage could handle that...

Craig,
How bad a design would it be if I chose to do it by putting two triggers from my first job seperately without using a sequencer as I mentioned earlier i.e. one each for OK , warning and then email the job status based upon the status of the job. And I would always have Exception Handler to report job failure. Writing a routine is something which I am new to and would like to avoid at this stage. If there are any inbuilt routines which I can call( I have looked at them but dont think the tool has one) and use it my job.

Thanks
Kris.

You'll need three - Ok and Warn to a Sequencer set to 'Any' to trigger your second job and then a third trigger of Warn type that goes to the reject Notification Activity stage. Ok just runs Job2, Warnings runs Job2 and sends the email.

That works Craig. Thanks.

Kris