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.
How to reject invalid records from a sequential file
Moderators: chulett, rschirm, roy
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.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
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.
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
[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.
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.