When working with Oracle, drivers are truly a pain to get working correctly. I will discuss my preferred choice and why for the following tools – SSIS, SSAS, and SSRS.
Without much question, you should use the Attunity tools for working with Oracle data in the Data Flow task. In SSIS 2008, the SSIS Connector is free and can be found here: http://www.microsoft.com/download/en/search.aspx?q=oracle%20connector. It includes the connection manager, source component and destination component. Without a doubt this is the only way to work with Oracle data components in the Data Flow task.
(NOTE: I cannot find the SSIS 2012 equivalent at the moment. However, Matt Massan’s blog post after PASS Summit 2011 notes more work is being done with Attunity.) UPDATE: I wrote this prior to a blog post from Matt Massan on support for SSIS 2012 with v2.0 of the Microsoft Connector. Check out Matt’s update on this: http://blogs.msdn.com/b/mattm/archive/2012/04/04/microsoft-connectors-v2-0-for-oracle-and-teradata-now-available.aspx.
However, this connector does you no good when working with the Execute SQL task. In SSIS 2008, I use the OLE DB provider from Oracle to create the connection used with Execute SQL Task. In my work with procedures in my first tip, I used the OLE DB provider with 2012 as well and it worked fine.
When working with SSAS 2008 and, until I know differently, SSAS 2012, I would recommend using the Oracle OLE DB driver from Oracle. This driver is not the fastest I have seen (third party drivers are marginally faster and the .NET driver is faster as well), but it has provided consistent results for the right price. Third party drivers will improve the throughput, but not substantially. The Oracle provided .NET driver is faster as well, but has an unchangeable active query timeout of one hour. If you have any processing times that exceed this, it will unceremoniously drop the connection. For these reasons, I have stuck with the OLE DB provider from Oracle which is not necessarily the fastest, but it has been the least painful to work with.
It is with SSRS I have seen mixed results. Primarily because of the better performance in the Oracle .NET driver. If you can guarantee that your reports will return their data in under an hour, this seems to be the best option. However, if you want to manage to a single driver set across all tools, you may find that the management of the OLE DB driver as the only driver makes sense in your organization.
Test, Test, Test
I have given you my experience using the drivers above. However, you may find value in purchasing a third party driver or you may find a different experience when you implement in your environment. Be sure to test and understand the implications in maintenance and system cost when choosing different drivers across your solutions.