0

Unfortunately nobody really has much knowledge about this source database, so while I was told that the incoming data was a FLOAT, it is appearing like that is not the case.

I have an incoming field with some sort of numeric datatype, and its value is 101.15000000000001

I need to get this into a SQL destination column of type numeric(6,4). The problem is that no matter what conversion I try, I continue to get type casting errors (0x80004005, or 0xc0049064). My current expression for the field is:

 (DT_NUMERIC,6,4)MY_FIELD

When I try to cast the value in SQL with this type I get an arithmetic overflow error, so I tried

 (DT_NUMERIC,6,2)MY_FIELD

This effectively truncates in SQL, however, I still get an error in SSIS. Any ideas on how I can truncate or round this value and get it into the numeric(6,4) data type?

Goose
  • 3,241
  • 4
  • 25
  • 37
  • 1
    Horrible hack, but maybe convert to a string and do some sort of substring that pulls 4 characters after decimal place and truncates the rest? It looks like that value is a float to me. How are you receiving the data? Text export? Direct query? Other? – Peter Schott Jul 05 '13 at 18:59
  • It is from an ODBC connection. The client sent us the data types he has on record but I think whatever DBMS he is using to view data is converting/transforming the actual underlying data types (which could be different). He claims that this field has a max length of 6 ,and maximum 4 decimal places. I actually tried converting to string and doing a substring on the value, followed by a type conversion but I still get one of the errors "Conversion failed because the data value overflowed the specified type." (0xC020907A) – Goose Jul 05 '13 at 19:07
  • you get casting errors in `Data Conversions` element ? – Justin Jul 05 '13 at 22:06
  • Would putting the values into staging tables (all varchar columns or some other concept) work? Could you pipe the "Failed conversion" rows to a different workflow with redirect on error, then analyze those rows elsewhere or just capture them as strings for later conversion? – Peter Schott Jul 05 '13 at 22:51
  • Yes, I've tried both Derived Column and Data Conversion. I have since redirected the failing rows elsewhere for the time being. The staging table idea may work, although I would like to avoid it if possible. I'll give it a shot next, thanks for your feedback. – Goose Jul 05 '13 at 23:26
  • 1
    Double click the line coming out of the source component and look at the metadata tab and find this column. What data type and precision does it report? For your supplied value, what do you want to store? Should it truncate, round up, round down, bankers round, etc? – billinkc Jul 08 '13 at 01:22

1 Answers1

-1

Try it in two operations:

string x = my_field
float y = (float)x

or float.Parse(x)

Metaphor
  • 6,157
  • 10
  • 54
  • 77