SQL Server - ORA Data Flow Task with High Data Volume

▲
0
▼

In my SSIS package I am trying to move data from SQL Server to Oracle using a Data Flow. The volume of data is high (4,000,000+ rows with around 25 columns) and I am getting an error sometimes. In case it runs successfully, it runs for a very long time.

OnError,,,PACKAGENAME,{4B45B43D-6F87-4B12-8B69-03954EF13CCA},,09/09/2014 7:10:44 PM,09/09/2014 7:10:44 PM,-1071636471,0x,SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80004005 Description: "ROW-00060: Internal error: [dainsert,16]孬u".

I have googled it and found some people suggesting Attunity connector. A few have tried out a third party Oracle connector that boasts of a lot of acceleration in the data flow. Are there any other solutions? Anybody who has successfully implemented a SQL Server-to-ORA DFT with a high data volume successfully ?

sql-server
oracle
sql-server-2012
asked on Stack Overflow Sep 11, 2014 by anonxen • edited Sep 5, 2019 by anonxen

3 Answers

▲
2
▼

Please note that, Microsoft connectors to Oracle by Attunity works only with Enterprise and Developer Editions of SSIS/MS SQL Server suite. If you have installed Standard Edition, then no chance on any destination components as the oracle provider is not supported for fast load options. I'm also struggling with terabytes of data load to oracle but running in chunks of millions.

answered on Stack Overflow Oct 24, 2016 by user2418133
▲
1
▼

I was initially using OLE DB Destination when I was getting that same error. I used ADO NET Destination instead and my data loaded successfully from SQL server to Oracle database.

SSIS.Pipeline: "component "ADO NET Destination" (49)" wrote 76506582 rows.

answered on Stack Overflow Mar 25, 2018 by Brian Sebastian
▲
0
▼

When loading data into Oracle using SSIS use of 'Microsoft Connector for Oracle by Attunity' can improve the performances. Although I do not have the statistics, my team has implemented this successfully in a project.

answered on Stack Overflow Oct 29, 2014 by anonxen

User contributions licensed under CC BY-SA 3.0