To skip a job for null dataset

gssr · Post by **gssr** » Tue Oct 20, 2009 5:29 am

Hi all,
I am using the target dataset of a job1 as an input of job2.I have skip the job2 if no records was passed to the target dataset in the job1
How can i achieve this,Can anyone suggest me a solution..

Thanks in advance

chulett · Post by **chulett** » Tue Oct 20, 2009 5:37 am

Use a sequence job to check either the size of the dataset or how many rows were written to it in the previous job and then only run the second job when that comes back non-zero.

ArndW · Post by **ArndW** » Tue Oct 20, 2009 5:43 am

Call job1 from a sequence job, after that completes do an execute command stage of "orchadmin describe -f {YourDataSetFileDescriptor}", then parse the output for the last line starting with " records" and then take the numeric value after the ":" character. You can then use this number of records to decide whether or not to call job2.

Another method to get the number of records in the dataset would be to use a call to DSGetLinkInfo() to get the number of rows that went down a particular link in job1.

gssr · Post by **gssr** » Tue Oct 20, 2009 5:43 am

We are running in daily cycle, So it will not be a null dataset.
The problem in my case is :The records of the last run was loading again in the dataset creating duplicate records.

Thanks

gssr · Post by **gssr** » Tue Oct 20, 2009 5:48 am

My requirement is : Not to load the last run records to the database if the current run produses no records.
Can this be achievable by any other way(By not skipping the job)

Thanks.

ArndW · Post by **ArndW** » Tue Oct 20, 2009 5:50 am

Use a call to DSGetLinkInfo() to get the information on the number of rows processed down a link.

chulett · Post by **chulett** » Tue Oct 20, 2009 5:51 am

Sounds like you may just need to make sure you have 'overwrite' turned on.

gssr · Post by **gssr** » Tue Oct 20, 2009 5:56 am

Actually, i am tring an option of skipping the job, But if there is any other option, i can try that too...
Hence i tried to clarify my requirement...

Thanks,

ljulienne · Post by **ljulienne** » Tue Oct 20, 2009 6:30 am

Otherwise you can create a technical field in your dataset which is different each time your job run and store this parameter in a technical table. Next before launching your job, you check if new records are present or not.

chulett · Post by **chulett** » Tue Oct 20, 2009 7:41 am

If you overwrite the previous dataset each time, loading it empty should not be an issue. Is there some reason you cannot simply do that?

HariK · Post by **HariK** » Tue Oct 20, 2009 7:46 am

If for some reason you need the past data then add a date field to data set.
In job1 set the derivation as sysdate for this field.
add filter on this field in the job2.

gssr · Post by **gssr** » Tue Oct 20, 2009 7:59 am

i am overwriting the file, but while loading the last run records are loaded...
Is ther any problem whiile overwriting the file without any records or do i have to change any properties in dataset ??

Thanks,

hemaarvind1 · Post by **hemaarvind1** » Tue Oct 20, 2009 8:46 am

you can create a new field in the job called 'modified date' which holds the date when the record is updated. If there are new records, compare the date with modified date. Add a filter such that only last loaded records are loaded. this can be achieved by comparing the record load date with the ,modified date column.

gssr · Post by **gssr** » Tue Oct 20, 2009 8:54 am

Thank you all for your replies,
But now i have a doubt about the dataset stage
:: I am overwriting a dataset here, If there are no records passed ,then the dataset should be emptied.But i am having the past run records. Why is it so....
Please clarify my doubt

Thanks to all once again,

chulett · Post by **chulett** » Tue Oct 20, 2009 10:41 am

Sending zero records to a dataset with the Update Policy set to Overwrite results in an empty dataset, regardless of what was in there before. Something else must be going on.