SSIS Package: Failed to open a fastload rowset for table. check that the object exists in database

Question

Hi, we have an SSIS package that includes a data flow task to retrieve the data from the OLEDB source(SQL Server) using SQL command, goes through sorting to remove duplicates, and stores it in the OLEDB destination(Oracle DB). The issue is with the OLEDB destination. the data access mode "Table or view - fast load" is throwing an exception whereas "Table or view" is not throwing any exceptions. Error while using "Table or view - fast load" :

While using "Table or view" the package is getting stuck at "execute phase beginning"

I have set the delay validation property to true and ValidateExternalMetadata to false for every component. Does anyone have any idea on how to fix the issue? Regards, Karthik.

Answer 1

Hi @Karthik Ponugoti, It seems that you are using ancient "Microsoft OLE DB Provider for Oracle". It is incapable to support the "...fast load" option, and has many other limitations. If your SSIS run-time environment is 2019 or later, and Enterprise edition, I would suggest start using Microsoft Connector for Oracle: https://learn.microsoft.com/en-us/sql/integration-services/data-flow/oracle-connector?view=sql-server-ver16#installation UPDATE Microsoft Connector for Oracle doesn't need Oracle Client installed. It is one of the huge benefits. Same with TNS files. No need. It is best to use EzConnect format: [//]host[:port][/service_name] with the Microsoft Connector for Oracle. Check it out here: https://learn.microsoft.com/en-us/sql/integration-services/data-flow/oracle-connection-manager?view=sql-server-ver16

Answer 2

@Yitzhak Khabinsky The package is stuck in the execute phase beginning and the data flow components remain in yellow color.