locked
C# copy 45 billiow rows from oracle to ms sql RRS feed

  • Question

  • Hi, My requirement is to dump 45 billion rows from oracle table to MS SQL. It is having 101 columns. If I try to do with ssis, am getting transaction log error after 12hours. 

    So,  i have decided to develop c# code. I have created a console application. I used below link to develop code. 

    link - https://sqlrob.com/2019/06/24/bulk-insert-data-from-oracle-using-c/

    Challenge - I'm having 45 billion rows in Oracle (source). How do I do batch wise rows insertion into MS SQL (destination) table. If i put select statement with 101 columns, will my dataset can hold that much data? How do I get data batch wise and then insert into destination table. I'm not expert c#. Appreciate your help.

    using System;
    using System.Data;
    using Oracle.ManagedDataAccess.Client;
    using System.Data.SqlClient;
     
    namespace DbInsert
    {
        class Program
        {
            static void Main(string[] args)
            {
                var startTime = DateTime.Now;
                Console.WriteLine("Start");
                string oracleConnectionString = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST={host})(PORT={port}))(CONNECT_DATA=(SERVICE_NAME={service})));User Id={user};Password={pwd}; ";
                string sql = "SELECT Id, Name FROM SourceTable";
     
                var dt = GetDataTable(oracleConnectionString, sql);
                Console.WriteLine("Record Count: " + dt.Rows.Count.ToString());
                InsertData(dt);
                Console.WriteLine("Stop");
                int timeSpan = (DateTime.Now - startTime).Seconds;
                Console.WriteLine(timeSpan.ToString() + " Seconds");
                Console.ReadLine();
            }
     
            private static DataTable GetDataTable(
                string connectionString,
                string sql
            )
            {
                var returnDataset = new DataSet();
                using (var connection = new OracleConnection(connectionString))
                {
                    connection.Open();
                    using (var command = new OracleCommand(sql, connection))
                    {
                        command.CommandType = CommandType.Text;
     
                        var dataAdapter = new OracleDataAdapter(command);
                        dataAdapter.Fill(returnDataset);
                    }
                }
                return returnDataset.Tables[0];
            }
     
            private static void InsertData(DataTable dt)
            {
                string sql = "Truncate table dbo.TargetTable;" + Environment.NewLine;
                string connectionString = "server={server};database={database};integrated security=sspi;";
                using (var connection = new SqlConnection(connectionString))
                {
                    connection.Open();
                    using (var command = new SqlCommand(sql, connection))
                    {
                        command.CommandType = CommandType.Text;
                        command.ExecuteNonQuery();
                    }
     
                    using (var bulkCopy = new SqlBulkCopy(connection))
                    {
                        bulkCopy.DestinationTableName = "dbo.TargetTable";
                        bulkCopy.WriteToServer(dt);
                    }
                }
     
            }
     
        }
    }


    • Edited by kdinuk Wednesday, September 30, 2020 6:14 PM
    Wednesday, September 30, 2020 5:59 PM

All replies

  • I certainly would not being doing it with C#, and I would post to a database forum asking how to do it. I am sure you'll find a DBA that has used DBA tools on both database platforms and knows the best approach.

    https://docs.microsoft.com/en-us/answers/products/sql-server

    Wednesday, September 30, 2020 7:06 PM
  • If I try to do with ssis, am getting transaction log error after 12hours. 

    So,  i have decided to develop c# code.

    Not the best decision, because it doesn't matter which way you go, the utilization of the transaction log file won't change and you arte going to trap into the same failure.

    Better use SSIS and optimize the process.

    - Set the DB recovery model to "Simply" or if it should stay in "Full", then increase the frequency of log backups
    - In SSIS data target set batch size & max commit size to a fitting size, e.g. 10,000 rows.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Proposed as answer by CoolDadTx Thursday, October 1, 2020 1:32 PM
    Thursday, October 1, 2020 5:58 AM
  • Hi kdinuk,
    user3289230 said that he can use this code example to copy data from Oracle to SQL Server successfully, so you can refer to.
    And have you tried to use SQL Server Migration Assistant to migrate Oracle Databases to SQL Server.
    SQL Server Migration Assistant (SSMA) for Oracle is a comprehensive environment that helps you quickly migrate Oracle databases to SQL Server.
    More details you can refer to this document.
    Best Regards,
    Daniel Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, October 1, 2020 6:23 AM
  • Hi kdinuk,
    Has your problem been solved? If it is resolved, we suggest that you mark it as the answer. So it can help other people who have the same problem find a solution quickly. 
    Best Regards,
    Daniel Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, October 16, 2020 7:10 AM