How to reject invalid records from a sequential file

[chivl627]

Hi,

I have a staging job which reads from a source sequential file (pipe delimeted) and then loads valid records into a staging table. The problem with the source file is, 2 or 3 records have incomplete number of columns. I want these rows to go to a reject file and only load those valid records in the staging table. Is there a way to filter these bad records? I guess I can use a constraint in the transformer, but what would the constraint be?

Appreciate your help in advance.

[kcbland]

Twiddle the setting on the Incomplete column in the column definitions of the sequential stage to Replace or Retain. If replace, you can give it default text. In the transformer constraint, look for your replacement text in the final columns to pick out the incomplete rows.

[at095nb]

You may use Count() function to define a constraints.
Count(InputRec,"|") will give you a number of pipe characters in InputRec. If it's less or greater then you expect, output the record to reject file.

[chivl627]

Hi,
Thanks!

I tried your suggestion Ken, and it's working.

at095nb, how do I define InputRec?

[chulett]

The 'bad' rows will never even make it to the transformer unless you handle the problem columns as Ken suggested.

Otherwise you need to bring the records in as one long string, 'validate' them in some fasion and then parse out the appropriate fields only on the rows that pass the validation.

[at095nb]

[quote="chivl627"]Hi,
Thanks!

I tried your suggestion Ken, and it's working.

at095nb, how do I define InputRec?[/quote]

In transformer define it as one string, VarChar(nnn). Put a constraints Count(InputRec,"|")=n for 'pass' link, where n-expected numbers of pipe;
Count(InputRec,"|")<>n for 'rejected' link. You will separate the file into two streams.