conversion error

Hi, can anyone please shed some light on this error:

[OLE DB Destination [466]] Error: There was an error with input column "Price" (518) on input "OLE DB Destination Input" (479). The column status returned was: "Conversion failed because the data value overflowed the specified type.".

The column "price" is a numeric (9)

In the flat file connection manager, the datatype for the price column is a float [dt r4]. I've also tried numeric, etc.

How do I resolve this error?

Thanks much

Don't you have a scale on that Price column? Can't a price have cents?|||

Yes, the price has cents.

|||

sadie wrote:

Yes, the price has cents.

But you said the field is NUMERIC(9). There's no scale, so the cents (decimal) can't be stored.|||

Hmm,

here's what the database says, and the data looks like: xxx.xxxxxxxxxxxx, so that's correct.

type computed length prec scale Price numeric no 9 18 12

|||Okay, so it's a NUMERIC(18,12)

Seems weird for a price field as it can only hold $999. Anyway, back to the problem at hand.... Do you have data that exceeds $999?

EDIT: I apparently can't do math, everyone. I still had "9" stuck in my head. 12 - 9 = 3. |||

I think I am missing something here.

According to this definition of the numeric datatype:

The numeric data type store numbers with a decimal place. When you use this data type you specify the precision (how many numbers total) and scale (how many numbers to the right of the decimal).

So wouldn't a numeric(18,12) be able to hold an 18 digit number with a MINIMUM of 6 digits on the left of the decimal point, and 12 digits on the right?

|||

Nevermind. There is a bad row in the data file. That is what is causing the overflow error.

Thanks

|||

sadie wrote:

I think I am missing something here.

According to this definition of the numeric datatype:

The numeric data type store numbers with a decimal place. When you use this data type you specify the precision (how many numbers total) and scale (how many numbers to the right of the decimal).

So wouldn't a numeric(18,12) be able to hold an 18 digit number with a MINIMUM of 6 digits on the left of the decimal point, and 12 digits on the right?

That is correct.

|||

sadie wrote:

I think I am missing something here.

According to this definition of the numeric datatype:

The numeric data type store numbers with a decimal place. When you use this data type you specify the precision (how many numbers total) and scale (how many numbers to the right of the decimal).

So wouldn't a numeric(18,12) be able to hold an 18 digit number with a MINIMUM of 6 digits on the left of the decimal point, and 12 digits on the right?

Yes, 18 specifies how many significant digits there are, while 12 of those 18 are to the right of the decimal point. Sorry, I'm losing my math mind, apparently.|||

No problem.

Your response gave me the idea to check my data file, and that's how I found the bad rows.