I have a vendor application database that is hosted on Oracle and one of the fields I need to extract has a data type of number (No scale, no precision - Just NUMBER).
When I do a preview of my SQL Query inside the OLE DB Source Data Flow Source, I see all of the data.
When I write to SQL Server (Field defined as numeric (18,8)) I #1 don't get the precision (all 0s) and #2, rows that have small values (no units) show up as all 0.000s.
I've tried to use a Data Conversion transform with no success and I also tried to CAST it using a derived Column again with no success. I tried to CAST it to a String in my Oracle Query using To_String() but then when I try to convert it back to a numeric in SSIS I get an error stating that I overflowed the data type.
This field shows work effort in hours and fraction of hours so I can't have 0s as effort but I can have seconds and minutes as fractions of an hour.
Any ideas out there? I'm getting Oracle-frustrated.
Which version of SSIS are you using? 2005/2008/x64/x86?
Try specifying a precision for NUMBER - that was the workaround for this issue in 2005, and that has been fixed in 2008.
check the following :- http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=282497