Scale issue with oracle Numeric types and OLEDB or datareaders
Hi,
I saw some threads here about related issues however I am still trying to find a workaround. I use the Sept CTP.
When pulling data with a query from oracle 8 or 9 against numeric [DT_NUMERIC] columns set with a scale 0f 2 on the Oracle side, I allways get a scale of 0 on the SSIS side. I tried datareader and oledb, it is the same.
When I use datareader, I can change the input column to a scale of 2 however i get an error when I try to change the output column to a scale of 2.
When I use an oledb, then I can change both input and output scale to 2 however I get an error for the Source error output column which must have the same scale. problem is that I cannot change the Source error output column scale, I get an error when I try.
as a consequence I am unable to retrieve the exact same values than in oracle, I miss the decimals for the numeric 38,2 out there.
So, what is this all about? I am missing something or is it a bug of the sept CTP?
Thanks.
Philippe
Philippe
[1020 byte] By [
Philippe] at [2007-12-17]
Yes, I saw this thread and grabed the usefull AlwaysUseDefaultCodePage = true tip from it.
For the scale issue, I am still out of luck. I installed the rtm developer version and still no luck with the scale issue.
I am using a SQL Command against an Oracle 8.1.7 database.
The command uses a SUM aggregate against NUMBER(13,2) column.
I tried to add a CAST (SUM(mycol) as NUMBER(13,2) as mycol to the SQL Command, still no luck.
Then, I tried to do a CAST(SUM(mycol) as VARCHAR(50) and used a Data Conversion data flow task to convert this back to Numeric(DT_NUMERIC) precision 18, Scale 2 and here, I get the correct value with the 2 decimals 
Hopefully there is a better solution to this issue than converting the data like this.
Thanks
Philippe
Yes, I did.
It is not possible to do this in a datareader it does not let you add an output column.
The oledb source does not let you add a column in the error output.
Also, after you did the column mapping, you have to get back to the column output to reset it to the precision and scale you want. The fact to map the column is overriding the initial setting.
I guess it is not going to work without the error output column or as a minimum would not be fully functional. With the workaround I mention, you got it all.
Bty, with oledb source you cannot use properties to create the sql command on the fly, you have to use a variable, this require an extra script step to build it.
It would be nice to have a more straightforward way to get the scale you want, may be in sp1?
Thanks
Philippe
Philippe wrote: