November 2, 2011 at 5:33 pm
Hi,
This might seem silly, but we are using SSIS to perform ETL from and to Oracle. We are doing this as the company has decided that Data Stage will no longer be supported, so instead of using Stored Procs, we are giving SSIS a try.
I am reading from Oracle tables, performing the number conversions and applying the CodePage setting change, and trying to load into another Oracle table. What I have noticed is that the OLE DB Destination is trimming or cutting some of the numbers (surrogate keys) and causing them to be totally different numbers. For example 3000 and 3100 are changed to 0, 2020 is changed to 20, 1080 is changed to 80 and 3680 is changed to 680.
The problem is that this is not happening for all rows. I'm trying to spot the pattern and to figure out why only certain rows' numbers are changed.
Has anyone else come across this issue?
Please help.
Thanks in advance-
November 3, 2011 at 8:50 am
I have never seen SSIS do any truncation when the data types were correctly sized. Are you working with these as number data types or as string data types? Are they corectly sized?
CEWII
November 3, 2011 at 2:47 pm
Elliott Whitlow (11/3/2011)
I have never seen SSIS do any truncation when the data types were correctly sized. Are you working with these as number data types or as string data types? Are they corectly sized?CEWII
I agree with Elliott. I've never seen SSIS do this, so I'd double-check my data types.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 3, 2011 at 3:43 pm
I am extracting from an Oracle Number data type, casting it as Number(18, 5) (because SSIS doesn't load the field without precision specified) and then loading it back to an Oracle Number data type field.
It doesn't do it for all rows. Most rows come through fine, but for some it just cuts one or two digits off. I also can't really pick up a pattern. At first I thought it had something to do with records that end in 00, but that fell away when it started cutting off digits for numbers like 3680. It would turn it into 680.
I have solved this by using an INSERT INTO statement in an Execute SQL component instead of an OLE DB Destination.
Still don't know what caused it.
Still wasted a lot of time.
November 4, 2011 at 2:41 am
Maybe check the advanced editor of the source component to check if datatypes are used correctly.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply