OLE DB Dest error for Oracle table with Fast Load option

Question

I got a simple DataFlowTask which pulls data from Netezza source and loads to Oracle Table through OLE DB Source/Destination components. The loads works well with Dest - Table or View drop down selecting the table to load and mapping columns.

However, this option is very slow since it performs record by record and commits transaction after each record insert. And since Table or View Fast Load option works at batch level and much faster, I wanted to configure this option and select the table from the drop down.

After the table is selected and click on Column Mappings tab, Error being generated

Note :

1. Load works very well for just Table or View option
2. Ensured, the OraOLEDB provider and its configured correctly in the registry
3. No issues on the datatypes- all maps correctly
4. Connection string is correctly pointing to Oracle Target DB and also cross checked for PersistSecurityInfo property : TRUE / FALSE no effect
5. Provider=OraOLEDB.Oracle.1; correctly referring to the one configured in registry

Only problem, Fast Load option is not working. version : SSIS 2012 / Oracle 11g / 32-bit package configuration on a 64-bit Windows machine.

Answer 1

To use fast Load Option for Oracle destination , Use Oracle Attunity Destination instead of OLEDB. I've used this and I observed 500X performance gain.

https://technet.microsoft.com/en-us/library/ee470675(v=sql.100).aspx

Answer 2

SQL Server 2008, 2008 R2, and 2012 (Enterprise and Developer editions) support bulk loading Oracle data using Integration Services (SSIS) packages. The Microsoft Connector for Oracle by Attunity provides optimal performance through their high-speed connectors during the loading or unloading of data from Oracle. For more information, see Using the Microsoft Connector for Oracle by Attunity with SQL Server 2008 Integration Services (http://msdn.microsoft.com/en-us/library/ee470675(SQL.100).aspx). SQL Server 2005 and the non-Enterprise and non-Developer editions of SQL Server 2008, 2008 R2, and 2012 don’t provide an out-of-the box option for bulk loading Oracle data.

The fast load options for the OLE DB destination aren’t available when you use the Oracle OLE DB provider for Oracle because the provider doesn’t implement the IRowsetFastLoad (http://msdn.microsoft.com/en-us/library/ms131708.aspx) interface. In addition, the current design of SSIS is such that it makes the fast load options available only for the SQL providers. The options aren’t available for any other provider even if the provider implements the IRowsetFastLoad interface.

The Microsoft OLE DB Provider for Oracle is deprecated and not recommended to use against Oracle versions later than 8i. http://support.microsoft.com/kb/244661

In SQL Server 2005 and the non-Enterprise and non-Developer editions of SQL Server 2008, 2008 R2, and 2012, the out-of-the box, SSIS components implement single row inserts to load data to Oracle. When you use single row inserts, the following issues may occur.

• Long load times and poor performance
• Data migration deadlines are not met
• Timeout during the ETL process for large production databases (greater than 500 GB) with complex referential integrity

For these releases, there are alternatives for achieving optimal performance when loading Oracle data. This paper discusses these alternatives.

Answer 3

It seems that Oracle driver you used doesn’t support Fast Load/Bulk Insert. For example, the Microsoft OLE DB Provider for Oracle doesn’t support Fast Load. You are using SSIS 2012 so you should us Microsoft connecter "Microsoft Connector Version 2.0 for Oracle by Attunity Jump" which is matching your Oracle version for better performance. I yet not tried but according to documentation it should support bulk load. See below link for details:

https://social.technet.microsoft.com/wiki/contents/articles/1957.ssis-with-oracle-connectors.aspx