Share via

'OraOLEDB.Oracle' failed with no error message available, result code: E_FAIL(0x80004005).

Subash Basnayake 61 Reputation points
2022-11-01T13:34:28.467+00:00

I have an SSIS package used to read data from an Oracle database and load it to the Azure SQL Server database. The SSIS package runs in the Azure data factory as an SSIS IR.
I am using the OraOLEDB.Oracle provider in the connection string.
There are many sources used in this data load and each runs as a separate SSIS execution. Some of the executions are failing with the above error message. I can see that some rows have been successfully transferred even for the failed executions. As an example, there is an execution which transferred 20000 rows and then failed with this message.

What could be the issue related to this? The error message is not helpful at all in finding a solution or root cause.

Not only is Azure SQL, the same error occurred in SQL server running in on-premise VM too.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,972 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,590 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 112.7K Reputation points MVP
    2022-11-01T22:19:44.433+00:00

    I don't know why this fails. Or even how to troubleshoot it. The challenge is that for such a task you need to know both sides. And I only know SQL Server.

    Where is the Oracle database? On-prem? Another cloud? At least, it is not in Azure. And for communication from/to Azure, you will always have to count with transient network issues.

    I note that you say that this happens on-prem too. But if the Oracle database is another cloud, you still have the same situation. And even if the Oracle database is local, there may still be network issues.

    It may be that you just has to accept things as they are and design your solution to handle. That is, retrieve row in reasonably sized batches, and if a batch files retry that batch. I can see that this seems like an overkill for something that runs on-prem only. But as soon as you involve a cloud, you more or less has do to this.

    0 comments
  2. ZoeHui-MSFT 37,441 Reputation points
    2022-11-02T02:08:00.023+00:00

    Hi @Subash Basnayake ，

    What's the version of your SQL Server?

    Microsoft Connector for Oracle enables the ability to export data from and load data into Oracle data source in an SSIS package.

    The following Microsoft SQL Server products are supported by Microsoft Connector for Oracle:

    Since SQL Server 2019 CU1
    SQL Server Data Tools (SSDT) 15.9.3 or later for Visual Studio 2017
    Microsoft SQL Server Data Tools (SSDT) for Visual Studio 2019

    Regards，

    Zoe Hui

    If the answer is helpful, please click "Accept Answer" and upvote it.

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.