Share via

SSIS ETL from Oracle to SQL, "Successful" but only transfers ~4Billion rows of 5.5B

Jason Allen 6 Reputation points
2022-09-14T17:04:10.317+00:00

I have several SSIS packages to transfer data from Oracle to SQL Server. On several of my very large (5-8 Billion rows) tables, SSIS will finish reporting success, but only transfer ~4.3 Billion rows, leaving the rest not transferred. It happens on every large table, leaving me to think its a limitation somewhere in SSIS. Anyone have experience or knowledge about this?

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,702 questions
0 comments

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. ZoeHui-MSFT 41,491 Reputation points
    2022-09-15T07:54:30.21+00:00

    Hi @Jason Allen ,

    May I know the version of Visual Studio and SQL Server?

    The following Microsoft SQL Server products are supported by Microsoft Connector for Oracle which will be more convenience.

    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

    If you are ignoring errors in your insert, then you will see nothing. You can redirect errors, count them to know the number of rejects, and store then somewhere to see what is being rejected.

    Here is a same thread you may take a reference to: https://www.sqlchick.com/entries/2012/9/2/resolving-missing-records-in-ssis-from-oracle-source.html

    Regards，

    Zoe Hui

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

    0 comments
  2. Jason Allen 6 Reputation points
    2022-09-15T15:37:27.513+00:00

    I am using SQL Server 2019 CU12, SSDT 16.0.62012 on VS 2019.

    I am not ignoring errors in the data flow

    Using the .NET OracleClient Data provider, which doesn't seem to have a UseSessionFormat parameter

  3. Jason Allen 6 Reputation points
    2022-09-15T17:09:44.74+00:00

    Actually, I just double checked and I am using the Microsoft Connector for Oracle. I had a .net one also defined from my initial dev work on it, but my actual data flow is currently using the Connector.

  4. Olaf Helper 47,436 Reputation points
    2022-09-19T07:38:50.133+00:00

    but only transfer ~4.3 Billion rows,

    = 2 ^ 32.
    There was a known bug in the ORACLE data provider, which really ends up after transferring 2^32 rows, but that bug was long time ago.
    You may install the latest version of ODAC.

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.