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 ?
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.
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.
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.
User contributions licensed under CC BY-SA 3.0