dbaSQL
asked on
decimal being rounded in v2008
Odd little thing I just noticed....
my datasource is SQL v2000, 'price' is the attribute in question, it is DECIMAL 9(18,8)
my destination is SQL v2008, 'price' is the same, it is DECIMAL 9(18,8)
I'm using v2008 SSIS to copy the data from source to destiniation (until we upgrade and get off the v2000 box). it's a flat file source, created from a simple bcp out of the v2000 box, into sql server destination. that's it. no conversions, no nothing.
but get this... my price is being rounded
source: 96.78000000
destination: 96.00000000
i've checked the current flat file. the price values are in there just fine, non-rounded.
and yes, I am certain that I am looking at the same record in source vs destination. there's a TON of data in this thing. i transferred upwards of 5M this morning, i just noted that price thing, so i've dug a little deeper and find that NONE of the prices equate. not just today's 5M, but everything I've checked. they've all been rounded down in the v2008 instance. like this:
source: 72.33000000
destination: 72.00000000
any ideas at all?
my datasource is SQL v2000, 'price' is the attribute in question, it is DECIMAL 9(18,8)
my destination is SQL v2008, 'price' is the same, it is DECIMAL 9(18,8)
I'm using v2008 SSIS to copy the data from source to destiniation (until we upgrade and get off the v2000 box). it's a flat file source, created from a simple bcp out of the v2000 box, into sql server destination. that's it. no conversions, no nothing.
but get this... my price is being rounded
source: 96.78000000
destination: 96.00000000
i've checked the current flat file. the price values are in there just fine, non-rounded.
and yes, I am certain that I am looking at the same record in source vs destination. there's a TON of data in this thing. i transferred upwards of 5M this morning, i just noted that price thing, so i've dug a little deeper and find that NONE of the prices equate. not just today's 5M, but everything I've checked. they've all been rounded down in the v2008 instance. like this:
source: 72.33000000
destination: 72.00000000
any ideas at all?
You need to put a conversion task in your ssis package to convert it to 4 decimal spots.
ASKER
But why, chap? the attribute is the same in the source and destination. why would a conversion be necessary?
because SSIS does some funky stuff sometimes....
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
>>Double check the data types and scale...
You are correct, Mr. Wills. Having checked the datatype, i was numeric. i'm unsure how that occurred, but i changed it within the source output definition. had to reload everyting, but all is well.
thank you both for looking
You are correct, Mr. Wills. Having checked the datatype, i was numeric. i'm unsure how that occurred, but i changed it within the source output definition. had to reload everyting, but all is well.
thank you both for looking
Happy to help....
BTW, how is that machine performing now ?
BTW, how is that machine performing now ?
ASKER
Performing exceptionally well. (knock on some wood)
Good to hear :)