Capture Reject Rows

arunkumarmm · Post by **arunkumarmm** » Mon Sep 20, 2010 3:48 pm

Hi,

I have a simple job, XML as source and sequential file as target with a transformer in between. Now, due to some extrodinary situations, the source records may have values more than the length assigned in the target stage. This is very rare but can occur. My requirement is to capture those records into a separate file as rejected records.

I created another link from the transformer and defined the constrain as MainLink.REJECTED but no rows are beeing passed on to this. Any thoughts? Or is there anyother way to achieve this?

chulett · Post by **chulett** » Mon Sep 20, 2010 5:19 pm

They won't be rejected but simply truncated. You'll need to check the field sizes yourself and use an explicit constraint to move them down your 'reject' link.

arunkumarmm · Post by **arunkumarmm** » Mon Sep 20, 2010 9:00 pm

chulett wrote:They won't be rejected but simply truncated. You'll need to check the field sizes yourself and use an explicit constraint to move them down your 'reject' link.

Thanks Craig. But I have 2000+ columns and not sure on which columns will be affected. Should I need to include all the columns in the constrain and check? There is no other way to achieve this?

chulett · Post by **chulett** » Tue Sep 21, 2010 6:51 am

If that's your requirement, yes. One other 'trick' you can try is to increase the size of the fields defined in the job so they are larger than your target field sizes which would allow those 'oversized' values to actually be sent to your database. It should then do an actual physical reject of the row and then you can capture it as you intended originally. Worth a shot, I suppose. Try a test on a single field and value first before you go all crazy on the 2000+ columns you have.

Keep in mind the fact that it will also generate a warning and enough of them will fail the job.

arunkumarmm · Post by **arunkumarmm** » Tue Sep 21, 2010 8:40 am

chulett wrote:If that's your requirement, yes. One other 'trick' you can try is to increase the size of the fields defined in the job so they are larger than your target field sizes which would allow those 'oversized' values to actually be sent to your database. It should then do an actual physical reject of the row and then you can capture it as you intended originally. Worth a shot, I suppose. Try a test on a single field and value first before you go all crazy on the 2000+ columns you have.

Keep in mind the fact that it will also generate a warning and enough of them will fail the job.

Thanks Craig. I will try this and let you know.

arunkumarmm · Post by **arunkumarmm** » Tue Sep 21, 2010 9:00 am

Any thoughts on using a ODBC stage to write the target file?

chulett · Post by **chulett** » Tue Sep 21, 2010 9:09 am

Sorry but my second suggestion isn't valid for your target type. There's nothing in the nature of a sequential file that would cause rejects, I lost track of that and thought we were talking about a database table.

Now, you could use a database table as an intermediate staging point, a work table in other words and allow it to do the rejecting for you. Afterwards, source any survivors from the work table and then write them out to your flat file.

chulett · Post by **chulett** » Tue Sep 21, 2010 9:10 am

arunkumarmm wrote:Any thoughts on using a ODBC stage to write the target file?

Using the text drivers? No clue how well that would work but might be worth experimenting with. If you give that a try, let us know.

arunkumarmm · Post by **arunkumarmm** » Tue Sep 21, 2010 10:04 am

chulett wrote:
arunkumarmm wrote:Any thoughts on using a ODBC stage to write the target file?
Using the text drivers? No clue how well that would work but might be worth experimenting with. If you give that a try, let us know.

I'm trying on that. Will let you know.