C# copy 45 billiow rows from oracle to ms sql

[kdinuk]

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 - Bulk Insert Data Into SQL Server 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);
}
}

}

}
}

Continue reading...