Aborting job if too few rows are processed
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 12
- Joined: Tue Mar 27, 2007 11:08 am
Aborting job if too few rows are processed
Does anyone have an elegant way to do this?
I have a job that pulls data from a database, runs through a single transform to do some formatting, then writes to a dataset. I would like this job to abort if the number of records passed is less than a certain amount.
I just don't see any way to make this happen directly in the existing transform (the amount of data is small enough I'm willing to force the job to process sequentially if need be).
What I've done is have that transform also write to an aggregator stage for a total row count, which then links to a second transform. That transform has an output link with a constraint that the passed count is less than the cut-off point - and then that constraint has "Abort after 1 row" set.
This works fine enough, but just seems a bit slap-dash.
Any other ideas/suggestions on a cleaner way to make this happen? Or some obvious approach that I'm overlooking?
-> Richard
I have a job that pulls data from a database, runs through a single transform to do some formatting, then writes to a dataset. I would like this job to abort if the number of records passed is less than a certain amount.
I just don't see any way to make this happen directly in the existing transform (the amount of data is small enough I'm willing to force the job to process sequentially if need be).
What I've done is have that transform also write to an aggregator stage for a total row count, which then links to a second transform. That transform has an output link with a constraint that the passed count is less than the cut-off point - and then that constraint has "Abort after 1 row" set.
This works fine enough, but just seems a bit slap-dash.
Any other ideas/suggestions on a cleaner way to make this happen? Or some obvious approach that I'm overlooking?
-> Richard
Re: Aborting job if too few rows are processed
What if in the select statement that you run on the database you get a count(*), then directly in the first transformer you could use the strategy you are using.
It took me fifteen years to discover I had no talent for ETL, but I couldn't give it up because by that time I was too famous.
-
- Participant
- Posts: 12
- Joined: Tue Mar 27, 2007 11:08 am
Sud:
I could certainly access the database and get a row count, that's true. But since I'm already pulling the rows into DataStage, I was hoping to minimize the need for any major change. I was hoping to just be able to make a few minor modification to the existing job and be done with it.
CCatania:
I was looking at doing a count or looking at the @ruwnum - but where I run into a wall with this approach is WHEN/HOW do I look at that count? I can't look at that count until the transform is "done" - since when the process first starts running, my counts will initially be under the threshold. But that's obviously not an abort situation since if the job keeps running, it will cross that threshold eventually.
Plus with the parallel job, the concept of a transform being "done" is a bit fuzzy - which is why I felt the need to go into an aggregator for a final count.
Thanks for the replies,
-> Richard
I could certainly access the database and get a row count, that's true. But since I'm already pulling the rows into DataStage, I was hoping to minimize the need for any major change. I was hoping to just be able to make a few minor modification to the existing job and be done with it.
CCatania:
I was looking at doing a count or looking at the @ruwnum - but where I run into a wall with this approach is WHEN/HOW do I look at that count? I can't look at that count until the transform is "done" - since when the process first starts running, my counts will initially be under the threshold. But that's obviously not an abort situation since if the job keeps running, it will cross that threshold eventually.
Plus with the parallel job, the concept of a transform being "done" is a bit fuzzy - which is why I felt the need to go into an aggregator for a final count.
Thanks for the replies,
-> Richard
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: