February 1, 2013 at 6:09 am
Hello all,
I am struggling with an issue regarding data transfer from Oracle (Release 11.2.0.1.0) to SQL Server (2008R2) via SSIS.
The Oracle source table has a column, lets call it num1, defined as datatype NUMBER in Oracle, with neither Precision nor Scale defined. According to Microsoft's recommendations for datatype mapping between Oracle and SQL Server I have declared the corresponding column in SQL Server as a float.
For the Oracle source data I am using an ADO NET Source SSIS dataflow component. When I connect it to Oracle and specify the table containing the num1 column and then look at the Output properties of this column, to my surprise SSIS maps/converts this column into DT_NUMERIC(38,4). What this means, if I understand correctly, is that SSIS limits the output of the column value to the destination to 4 decimals and if the value in Oracle contains more than 4 decimals, a rounding of the value will occur. I have observed this behavior using these two .NET providers:
- System.Data.OracleClient (The Microsoft one that ships with .NET but has been stated as "deprecated")
- Oracle.DataAccess.Client (The one provided by Oracle in ODAC)
Using either of those two providers, the value "1.12345" stored in a NUMBER-column in Oracle will be rounded already in the data flow between Oracle and SSIS, and stored in the corresponding SQL Server column as "1.1235". As I described earlier I have declared the SQL Server column as a float, but just to eliminate the possibility that the rounding of the value should occur in SQL Server, I have also tried with the SQL column being declared as a NUMERIC(38,10). The same result is achieved in both cases which indicate that the rounding is done before the value is stored in SQL Server.
One interesting thing is that when I tried the same scenario using an OLE DB Source component and using the MSDAORA (Microsoft OLE DB provider for Oracle) instead, SSIS mapped the very same column as a "DT_R8", which, according to this nice datatype chart provided by Devin Knight, corresponds to a float datatype, which seems to be more in line with Microsoft's recommendations on the link above.
I wonder if anyone can explain to me why the two .NET providers from Microsoft and Oracle maps Oracle NUMBER-columns (without precision and scale defined) to NUMERIC(38,4) and thereby effectively limits the output precision of the column value to 4 decimals?
Also, can anyone see a workaround for this if we want to continue using the Oracle .NET provider (as recommended by Microsoft) and at the same time avoid rounding of the value in SSIS? We cannot change the Oracle column definitions, those are out of our control. Do we have enter the world of CONVERT's in the select-statement against Oracle? I would rather avoid this but if it's the only way to go to preserve the precision of the value from Oracle, we might as well have to look into this.
Thankful for any input,
Martin
October 8, 2014 at 3:27 am
Same problem here (SQL 2012):crying: The old data client worked fine in 2008 but the new ADO.NET convert NUMBER to Numeric(38,4) wich should have been REAL.
We solved it to make views on Oracle and cast the NUMBER colums.
CAST(ROUND(<column>,4) AS DECIMAL(38,4))
October 8, 2014 at 4:08 am
fokkol (10/8/2014)
Same problem here (SQL 2012):crying: The old data client worked fine in 2008 but the new ADO.NET convert NUMBER to Numeric(38,4) wich should have been REAL.We solved it to make views on Oracle and cast the NUMBER colums.
CAST(ROUND(<column>,4) AS DECIMAL(38,4))
If the mappings bother you, you can change the defaults in the mapping files.
You can find them for example in this folder:
C:\Program Files (x86)\Microsoft SQL Server\110\DTS\MappingFiles
Find the one for Oracle and SSIS and change the data type mapping to your needs.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 9, 2014 at 2:57 pm
Did not know about that, thanks koen.
But the problem here isn't really the mapping 'in' SSIS, but SSIS detecting the source column as NUMERIC(38,4).
We only have 1 oracle system, so don't want put to much time in it. But it seems there are a couple of options for the oracle connector.
ADO.NET - OracleClient Data Provider (NUMBER > NUMERIC(38,4)) This types in System.Data.OracleClient are deprecated and will be removed in a future version of the .NET Framework.
OLE DB - Oracle provider for OLE DB (NUMBER > String:unsure:) (Mircosoft suggest this, but it seems to matter wich oracle client you have 10g,11g and to string...really?)
OLE DB - Microsoft OLE DB provider for Oracle (NUMBER > REAL), this seems good but gives me trouble @ SQL2012 x64 - This feature will be removed in a future version of Windows.
October 10, 2014 at 12:28 am
fokkol (10/9/2014)
Did not know about that, thanks koen.But the problem here isn't really the mapping 'in' SSIS, but SSIS detecting the source column as NUMERIC(38,4).
Is it possible to just change the data type in the advanced editor of the OLE DB source?
fokkol (10/9/2014)
OLE DB - Microsoft OLE DB provider for Oracle (NUMBER > REAL), this seems good but gives me trouble @ SQL2012 x64 - This feature will be removed in a future version of Windows.
Yeah, if I'm not mistaken, that one is indeed deprecated and there is no 64-bit version.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 10, 2014 at 6:40 am
Koen Verbeeck (10/10/2014)
fokkol (10/9/2014)
Did not know about that, thanks koen.But the problem here isn't really the mapping 'in' SSIS, but SSIS detecting the source column as NUMERIC(38,4).
Is it possible to just change the data type in the advanced editor of the OLE DB source?
It is possible, but once you refresh the metadata of the data source is goes back to numeric(38,4).
Next Oracle project I will invest more time in the ODAC (Oracle Data Access Components), because that seems the way.
Link below, to get the the ODAC version for the differnt oracle version (11g,12g ect..)
http://www.oracle.com/technetwork/topics/dotnet/utilsoft-086879.html
October 10, 2014 at 6:58 am
fokkol (10/10/2014)
Koen Verbeeck (10/10/2014)
fokkol (10/9/2014)
Did not know about that, thanks koen.But the problem here isn't really the mapping 'in' SSIS, but SSIS detecting the source column as NUMERIC(38,4).
Is it possible to just change the data type in the advanced editor of the OLE DB source?
It is possible, but once you refresh the metadata of the data source is goes back to numeric(38,4).
Next Oracle project I will invest more time in the ODAC (Oracle Data Access Components), because that seems the way.
Link below, to get the the ODAC version for the differnt oracle version (11g,12g ect..)
http://www.oracle.com/technetwork/topics/dotnet/utilsoft-086879.html%5B/quote%5D
Doesn't the ODAC installation just install the Oracle provider for OLE DB?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 13, 2020 at 10:58 am
We have exact same issue - how this was fixed? - there is no clear answer in this discussion.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply