0

While running a mapping I am getting couple of database errors and jobs failed 1.) Arithmetic Overflow error 2.) Conversion failed when converting date and/or time from character string.

This is purely data issue(datatype error and data length issue) and I want to reject these records and write it in a separate error table.

The .bad files in which these records are written consists of characters which looks like junk (',N,N,N,N' AND ',D' AND ',0'), I am not sure on what basis we get these characters.

  • Do we get this for null values? and how to overcome this and get the exact output?

  • Is it possible to write these rejected records directly to a relation table(error table with same structure as the target table) or a way around to achieve this?

Venkat
  • 47
  • 7

2 Answers2

1

You could use a router transformation to route every field which does not meet your criteria to the error table. This way you will handle them before they become bad rows.

stickabee
  • 145
  • 8
  • I don't have any conditions/criteria, it is just that these records gets rejected at target database due to database errors(which i have mentioned above). I don't think we can capture these within the mapping right? – Venkat Nov 14 '19 at 04:57
  • Above you mention datatype and length errors, those are the conditions I was referring to. They would be easy enough to test for with expressions. For example if you have a varchar(20) field then route anything with length > 20 to your error table. Everything else can flow to target as normal. – stickabee Nov 14 '19 at 13:06
  • Thanks let me try this. – Venkat Nov 15 '19 at 14:41
1

Hey Vankat just lookig at you problem try to filter out the records which doesn't meet your criteria by putting condition like (data type,length)at router transformation and route them to error table or capture that recods in a flat file. Hope this will give you a clear picture.

abhishek dubey
  • 11
  • 2