May 7, 2008 at 8:28 am
I am using oledb provider to connect to oracle an pull data from a view. I can map all columns except two Number type columns(dt_numeric) as it says it has invalid precision should be between 1 and 38.
when i look at advanced editor the data type is set to dt_numeric with a precision of 0. If i try change the precision it gets changed back. I phoned the oracle team and they say the datatype is a number and it uses the default precision of 38. Can anyone tell me how i can import these columns and what might be the problem with the data source?
May 7, 2008 at 9:31 am
I like Oracle, but the problem is the Oracle client. Even the 10g client I am currently using is a bit of a piece of garbage - it has this problem as well as a few others. I think it is just not fully OLEDB compliant on a few data types.
SSIS is pretty strict on data types so it really cares when something is not quite perfect.
You need to CAST the columns from Oracle to something compliant. In many cases, I have found it easiest to cast them to VARCHAR and then use a derived column or data conversion component to fix the data types within SSIS. This also makes it easier for me to handle data type changes on the Oracle end - if a precision changes I am still OK because SSIS just saw it as a string anyway.
May 7, 2008 at 11:11 am
Thanks for the reply, i think you are correct. I donot have access to the datasource and the people who do donot seem to know how to change the view.
I have ended up using a .net provider for oracle and the data reader component. this works as it seems to be not concerned with the exact data type, although i believe it is not as fast as the oledb provider.
May 7, 2008 at 12:10 pm
You can get away with that for some components, but if you need to use a lookup you will need to deal with the OLEDB provider.
If that ends up being the case later - keep in mind that the joins for lookups, merge joins, and any other "join" type component in SSIS are very data-type sensitive and also case-sensitive.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply