Oracle Provider for OLEDB SSIS Datatype conversion

Question

I am experiencing data type issues when using Oracle Provider for OLEDB.  The error that I get is Error: 0xC002F210 at Execute SQL Task: Executing the query "SELECT CODE_DOC_TYPE.NAME, CODE_DOC_TYPE.VALUE, D..." failed with the following error: "ORA-00923: FROM keyword not found where expected". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.   Task failed:  The query runs fine from TOAD and connection is established. 

I am updating an SSIS package to use the Oracle database instead of extracting data from oracle, saving as an excel file, then loading the excel file to a temp table on sql server 2012 table.  I changed my datasource initially to Microsoft Provider for Oracle, experienced some issues but then was advised to use Oracle Provider for OLEDB.  As far as best practice should I load my results in a temp table then use the execute sql task to query the temp table to form the resultset?

My data fields are of type number and char on oracle.  on the Advanced editor of the Data Flow tasks they were the number fields were defaulting to DT_WSTR.  Since then using info I found in forum, I changed my number fields to CAST(MY_FIELD AS NUMERIC(5,0)) so that the number is precise and when i look at the Advanced Editor Input/Ouput properties for output columns the SSIS Datatype is DT_NUMERIC....but I have a question on what datatype to use in my variable.  Prior to updating the connection manager to oracle, I was using variable as double datatype.  The variable helps in ending a loop that's setup up later in the process flow.

Answer 1

The error thrown by the Execute SQL Task is from the Oracle end.

If I go to their KB I get the following advise to resolving it:

Correct the syntax. Insert the keyword FROM where appropriate. The SELECT list
itself also may be in error. If quotation marks were used in an alias, check
that double quotation marks enclose the alias. Also, check to see if a reserved
word was used as an alias

This is your first issue to tackle.

The connection appears to be working, I also believe you are going to get the package executing after fixing this nuance.

As what it comes to best practices, Excel should not be used, instead, you must be able to freely load the data straight to the temp table on SQL Server. Just Bing (or Google) for examples on how to load data from Oracle and you will get a ton of links.

Since you pull data from Oracle, the datatype of SSIS DT_WSTR must work fine for any char Oracle datatype and DT_Numeric without any casting in PL/SQL. 

Lastly, do not let yourself get fooled by TOAD - it is a specialized GUI tool that only shows you the Oracle side world, not what happens with data in transit.

---

Arthur My Blog

Answer 2

Thanks Arthur,  the aliases are in double quotes and no reserved words are used.  The statement runs ok in my sql editor.  I reviewed the statement again and removed the comments.  After removing the comments it ran successfully.