July 19, 2010 at 1:42 pm
I have a SSIS package I'm developing against an Oracle datasource. While trying to incrementally load my dimension, I need to lookup to compare the Oracle source data against the data already in my dimension inside SQL Server 2005. I'm having trouble getting my lookup to work...everything returns null. The Oracle source data I'm trying to join on is of type Number 22, so I made the number fields inside my SQL Server dimension Numeric(22,0). I don't see any leading or trailing spaces, and I'm casting the Oracle data as Numeric(22,0) inside my Select statement, but things still won't match up. Are there any wierd tricks to looking up against Oracle Number types?
July 19, 2010 at 6:37 pm
cmbrooks08 (7/19/2010)
I have a SSIS package I'm developing against an Oracle datasource. While trying to incrementally load my dimension, I need to lookup to compare the Oracle source data against the data already in my dimension inside SQL Server 2005. I'm having trouble getting my lookup to work...everything returns null. The Oracle source data I'm trying to join on is of type Number 22, so I made the number fields inside my SQL Server dimension Numeric(22,0). I don't see any leading or trailing spaces, and I'm casting the Oracle data as Numeric(22,0) inside my Select statement, but things still won't match up. Are there any wierd tricks to looking up against Oracle Number types?
Oracle's NUMBER(22,0) should translate as SS2K5's NUMERIC(22,0)
I would query a specific Oracle database table/row from both Oracle and SQL Server sides and check if returned values are the same.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.July 20, 2010 at 5:39 am
Thanks for the reply Paul. I've queried both for a specific ID, and the fields do come back the same. Any other suggestions?
July 20, 2010 at 10:06 am
cmbrooks08 (7/20/2010)
Thanks for the reply Paul. I've queried both for a specific ID, and the fields do come back the same. Any other suggestions?
I would build a test case.
Create a simple one-column table on each side, number(22,0) on Oracle side and numeric(22,0) in SS2K5 side, populate two or three rows with the same values on each side like: 11111, 2222222222, 3333333333333333333333 - testing different data sizes.
Finally write a query on SS2K5 side joining the tables...
- if it works I would dig deeper on SSIS code.
- if it doesn't work you have a test case to show to Microsoft at the time of opening a ticket 🙂
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.September 23, 2010 at 3:28 am
Try to use TO_NUMBER(numeric_column) explicitly in your oracle datasource/lookup query.
Once, i have same numeric problem with openrowset query to oracle.
hope this help 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply