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
-- dummy table in Oracle
dual 

you will take the following as input for OLE DB Provider instead:

select dummy from --dummy table in Oracle dual

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.