File Validation Logic

fabiosantos · Post by **fabiosantos** » Tue Dec 27, 2016 8:55 am

Hi all,

Can you please help me with the logic for the following requirement

Source - Sequential File

Target1 (Database) - if all rows validate as OK

Target 2 (Sequential File) - if 1 or more rows validate as Non OK

After read a delimited file and validated, if 1 or more rows Non OK, all rows from source file should be moved to a bad file, adding the rejection reason for the bad rows.

The validation includes datatypes, range of values, number of delimiters.

If the all rows be OK, persists all rows in database.

Also, in the end of the process, is required update the file status in database as OK or Failed.

Thanks in Advance

UCDI · Post by **UCDI** » Tue Dec 27, 2016 9:45 am

sounds like design more than logic, but I would have my validation "job" append 2 columns (valid true/false, reason (null for valid rows)). Then write that to a dataset. Now flow into job #2 that reads the dataset, sorts off the valid column so that invalid rows are first, and checks the first row. This result will be passed into job #3 which reads the dataset again, and sends all the rows down 1 of your 2 paths by checking the output of job 2 to decide. Something like this...

(this is over-modular, you can do it in 2 jobs with the same concepts if you prefer. My team makes a lot of small, reusable modules so by habit I break things down).

qt_ky · Post by **qt_ky** » Tue Dec 27, 2016 9:50 am

It sounds like you will need at least 2 or 3 jobs. What have you tried so far?
At what point are you stuck on?

fabiosantos · Post by **fabiosantos** » Fri Dec 30, 2016 5:06 am

Hi UCDI,

How to create a Data Set file? I don't have the data set file yet.

UCDI · Post by **UCDI** » Fri Dec 30, 2016 10:35 am

I am on 11.x but I think its been around a while.
Under files in your stages for parallel jobs you should see dataset as an option.

The file type really does not matter too much. You are not even required to land in a file, that is just an example of the logical steps to take. You can aggregate or do a variety of other things to make a binary choice on what path to take though the code.

chulett · Post by **chulett** » Fri Dec 30, 2016 4:41 pm

Datasets? They are a key parallel framework component so they've been around... forever. I wasn't sure what you meant by you "don't have it yet", guessing that you did not mean the actual stage, yes? If you just meant you haven't used one yet, that's a whole 'nuther kettle of fish and easily solved.