I know that it is too small for article. I just can not put its HTML to tip - I see all tags and others in preview... So if you dont agree that it is article, just walk through. Think about those who really needs this information, at least the guy from this thread. Thanks.
Introduction
I had to migrate some SSIS packages with ADO.Net datasources to OLE DB datasources because of x64 and Oracle. All packages were workable, so I was surprised when I had got Datasource errors after switching to OLE DB Datasource. They were:
- ORA-00907: missing right parenthesis
- ORA-00903: invalid table name
- ORA-00904: string: invalid identifier
Solution
OLE DB Provider packs all query to single string, therefore if you have a comment in query, the rest of query will be truncated. So just avoid using of comments in query text that you intend to send to OLE DB Driver.
Code
If you v got well formatted SQL query something like that:
select dummy
from
dual
you will take the following as input for OLE DB Provider instead:
select dummy from
that I persume is not exactly what you expected :)
History
As soon as I got problem with OLE DB Datasource, I had grabbed the contents of the string that is passed in to the query field and had thoroughly examined it and there are no missing parens, all tables exist, all aliases are correct. Additionally, I was able to execute the select statement in Toad for Oracle, without difficulty. So I was sure that the string is syntactically correct.
First hypothesis was that something fishy is happening in the provider - perhaps the string is too long and it is getting truncated somewhere, but I had much longer query that executed successfully in the same package inside OLE DB Datasource.
After several hours of examining of problematic query (it numbered 11k of characters), I selected query of small size and where problem was reproduced. It became obvious that problem is in comments I had in query.
So I deleted all comments in query and problem was solved.
