SQL Server 2008 and SQL Server 2008 R2 (Enterprise editions) support bulk loading Oracle data using Integration Services packages with the Microsoft Connector for Oracle by Attunity. For SQL Server 2005 and the non-Enterprise editions of SQL Server 2008 and 2008 R2, there are alternatives for achieving optimal performance when loading Oracle data. This paper discusses these alternatives http://msdn.microsoft.com/en-us/library/hh923024.aspx.
We’ve documented this Whitepaper, which discusses the ways of implementing BULK INSERT functionality to Oracle in SSIS Packages.
Author : Snehadeep , SQL Developer Engineer , Microsoft
While technically this whitepaper is of a high standard, I don't think it offers solutions that should be encouraged:
* The script component implies lots of duplicated embedded code to meet a typical requirement - loading many tables.
* Third-party components often have question marks around quality, performance and support
Rather than writing white papers, I'd prefer to see Microsoft putting some development effort into either resurrecting their own provider for Oracle or fixing their integration with Oracle's provider. Please fix this glaring issue.
The current scenario is a farce, and it has been this way for 6 years now. I am embarrased to explain it to my clients - who are Microsoft customers.
I agree to your thoughts Mike. I would like to inform you that the BULK LOAD functionality should be implemented on both sides, the SSIS and the provider side as well. There are 3rd party providers who do not implement required interfaces to perform BULK LOAD would not be able to fast load data even if SSIS offers the ability.
One of the major focuses on Denali release is working with partners, spreading the awareness and encouraging them to implement the required interfaces in their provider code and take advantage of BULK LOAD through SSIS.
Currently SSIS can now Fast Load data to DB2 and Netezza and I'm hopeful that soon this would be the case with a few others too.
Note: Microsoft OLE DB Provider is dead and there aren't any future plan for it either, and, of course, SSIS Enterprise editions can always take advantage of the Attunity connectors.
This paper has been updated to apply to SQL Server 2012. The URL is the same,msdn.microsoft.com/.../hh923024.aspx