Driver Sources for Oracle
Here is my preliminary list of what works against Oracle....
OLE DB Source - use the Oracle Driver, but be cautious of Numerics and use a data viewer to make sure that numbers to the right of the decimal come through...
Lookup - Use the Microsoft Driver...using the Oracle driver will cause mismatches of all records when comparing numbers, like when looking up keys.
SCD - Oracle OLE DB Driver...no other choice, because the Microsoft driver can't handle parameters
OLE DB command - Same as above, as you need parameters
OLE DB Destination - use the Microsoft driver, as numbers ending in 0 (zero) will be turned into zeroes!!!
Cheers!
Scott
posted by Scott Barrett @ 2:03 PM
7 comments
7 Comments:
Hi,
Interesting all the problems you have been having with Oracle. I am using a custom script in the data flow task to access an Oracle SP that returns a refcursor that populates an OracleDataReader. I'm then sending this to an OLE DB destination (SQL Server - cannot get the SQL Server destination working!). Now I am getting ORA-03113 errors on big rowcounts and my dba team are telling me this is a red herring after digging around in the Oracle db and finding no problems and no network problems either. Any ideas what driver/provider I should be using?
Colin,
If you are using parameters, then you will need to use the Oracle for OLEDB driver. There should not be an issue with large resultsets as we routinely pull 10's of millions of records. The Data Reader is going to be pretty slow, so I would go to and OLE DB Source and use a variable to create the statement that it executes.
Scott
The problem is that I am having to execute an Oracle Stored Procedure to return a ref cursor (business decision to enscapulate data and query) - hence using the datareader within a custom script. Can you use a ref cursor along with the ole db source?
Colin,
You can't call it directly from the OLEDB source. I would create a stored proc on Oracle and just call it from the OLEDB Source. That source does not understand the nuances of sources that well other than SQL Server....
BTW, I have a good link on how to do this that I am trying to find for you. I will post it when I find it...
Scott
Hi Scott,
I am using Execute SQL task for reterving data from a table with CLOB field, this field is having XML. I am getting the error.
"ORA-00932: inconsistent datatypes: expected - got CLOB"
The behaviour is strange, this error occurs only when i put clob field at first position. If i move it to different position then its working.
Could you through some light on this.
I need to get records and fill it into ADO recordset for parsing the xml row by row.
SSIS cannot handle CLOBS....sorry! You will need to find another method to extract it to file to use....
Scott
Hi Scott,
I'm new in SSIS and I tried to put my sql statement in a variable like you explain but I must do something wrong because It doesn't work.
When you tell us to put the sql statement in the expression of the variable do we still put the ? sign for the parameter in the sql statement ?
Also, have you found another solution since then ?
Thanks
Post a Comment
<< Home