March 24, 2008 at 11:40 pm
Hi All
Please help me. I am sourcing the information from Oracle using ORA client on the MSSQL server.
When I stick the OLEDB Source and type SQL command in there such as
SELECT * FROM schema.some_view
It raises heaps of errors such as
Error at "..........." The output column "COL_ID" (3207)" has a precision that is not valid. The precision must be between 1 and 38.
It works for some other dataflow tasks which query plain tables.
The difference between the sources on Oracle side is that the view that makes SSIS fail is a SELECT SUM... GROUP BY... statement but the ones which does not fail, just simple SELECT * FROM ... statements.
Please help me, for I cannot solve the problem. It looks like SSIS cannot retrieve the metadata for the Oracle view, which has GROUP BY statement.
PS: I use ORA CLIENT 10
M.Sc.IT, M.B.A, MCTS BI 2008, MCITP BI 2008, MCTS SQL Dev, CSM, CDVDM
March 25, 2008 at 11:07 am
I have had this issue as well. I'm blaming it on the oracle client not working correctly. I think it is reporting information incorrectly to the OLEDB driver. When you group and sum data, you are creating new fields and oracle assumes a data type based on the data being aggregated.
The best way to deal with this is to CAST your output to better data types that the Oracle client can correctly report.
March 25, 2008 at 3:41 pm
Thank you. You are right. desc 'viewname' against the view which has calculations and aggregations in oracle gives a bald NUMBER agains the SUM and AVE and NVL and other calculated fields.
Adding CAST solved the problem .
Apparently SSIS builds its fields mapping in the OLE DB Source using the metadata returned by the connection and ORA client does not give it a correct definition of NUMBER.
I was wondering if I could specify the input fields and their precision manually in SSIS, rather than leaving it up to SSIS to figure it out?
Anyways, another thread on this is here, just to doublecheck
M.Sc.IT, M.B.A, MCTS BI 2008, MCITP BI 2008, MCTS SQL Dev, CSM, CDVDM
March 26, 2008 at 5:37 am
In some respects, you can. If you right-click on an OLEDB source and open the advanced editor the field definitions can be configured. You could even spend the time to set them up entirely, enter your query, and turn off checking the meta-data.
It's a pretty ugly way to manage this though and you run the risk of double-clicking on the component at some point and having the basic editor overwrite some of your setup. I would not recommend it.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply