OK....how do I get data out of an Oracle Database using SSIS?

Ok...this is going to be a series....

I've struggled through this in so many configurations that it's driving me crazy. Let me break it down to you so you can get some work done.

1. DataReader Source - Use the ADO.Net for ODBC and set up an ODBC connection. This is by far the fastest I have seen. Why??? The OLE DB layer has too much in the middle to make this fast. This method also solves a big problem with OLE DB and Oracle: NUMBERs!!!
2. OLE DB Source - This works, but use the Microsoft for Oracle Driver. Why? It sorts the tables when using OpenRowset, and it handles that pesky NUMBER problem.

So what is the problem with NUMBER?
In Oracle, the NUMBER is like a Variant data type. It can represent any kind of numeric data type. SSIS and other Microsoft tools will see this as a DT_Numeric and blow up if the precision and scale is not set. Why? Because Microsoft will always try to determine the exact data type to make SSIS more efficient. Using the Oracle for OLE DB driver, it will see a NUMBER with no precision or scale specifically set at Numeric(0,0). Now we all know that this will break, because you can't create that! The Microsoft driver goes the other way and defaults it to a Numeric(38,0), the max range. The problem here is what if you had numbers to the right of the decimal.....they're gone!

The fix here is to go in and change the Output columns to a DT_R8, Double Precision Float. This is done in the source and will get your numbers to you right. Now in the real world, people who build Oracle tables would set this properly and it would then be read properly by SSIS.

Go check your vendor apps and see how many NUMBER fields don't have the size and scale set....you'll be as stunned as me.....

SB