Loading 50 million records in 50 minutes in SQL Server - Super fast data loading

 Greetings to all the readers!!

 Yes, it is possible to load 1 million of records in 1 minute time from one SQL server to another.

•  Create an SSIS package with a Script task.

•  Add logging for ease of debugging and make all the connections configurable to make it re-usable.

•  In the script task, we have to use .net script given below. We can define the batch size and   timeout.

•  By Default, Identity in the destination is reset, in case you have to preserve it, amend the code      accordingly.

In my task, I used SQL Server 2017, SSIS 2017 and the source system is SQL 2008 R2

 C#

using System.Data.SqlClient;

class Program

{

    static void Main()

    {

        string connectionString = GetConnectionString();

        // Open a sourceConnection to the AdventureWorks database.

        using (SqlConnection sourceConnection =

                   new SqlConnection(connectionString))

        {

            sourceConnection.Open();

            // Perform an initial count on the destination table.

            SqlCommand commandRowCount = new SqlCommand(

                "SELECT COUNT(*) FROM " +

                "dbo.BulkCopyDemoMatchingColumns;",

                sourceConnection);

            long countStart = System.Convert.ToInt32(

                commandRowCount.ExecuteScalar());

            Console.WriteLine("Starting row count = {0}", countStart);

            // Get data from the source table as a SqlDataReader.

            SqlCommand commandSourceData = new SqlCommand(

                "SELECT ProductID, Name, " +

                "ProductNumber " +

                "FROM Production.Product;", sourceConnection);

            SqlDataReader reader =

                commandSourceData.ExecuteReader();

            // Open the destination connection. In the real world you would

            // not use SqlBulkCopy to move data from one table to the other

            // in the same database. This is for demonstration purposes only.

            using (SqlConnection destinationConnection =

                       new SqlConnection(connectionString))

            {

                destinationConnection.Open();

                // Set up the bulk copy object.

                // Note that the column positions in the source

                // data reader match the column positions in

                // the destination table so there is no need to

                // map columns.

                using (SqlBulkCopy bulkCopy =

                           new SqlBulkCopy(destinationConnection))

                {

                    bulkCopy.DestinationTableName =

                        "dbo.BulkCopyDemoMatchingColumns";

                    try

                    {

                        // Write from the source to the destination.

                        bulkCopy.WriteToServer(reader);

                    }

                    catch (Exception ex)

                    {

                        Console.WriteLine(ex.Message);

                    }

                    finally

                    {

                        // Close the SqlDataReader. The SqlBulkCopy

                        // object is automatically closed at the end

                        // of the using block.

                        reader.Close();

                    }

                }

                // Perform a final count on the destination

                // table to see how many rows were added.

                long countEnd = System.Convert.ToInt32(

                    commandRowCount.ExecuteScalar());

                Console.WriteLine("Ending row count = {0}", countEnd);

                Console.WriteLine("{0} rows were added.", countEnd - countStart);

                Console.WriteLine("Press Enter to finish.");

                Console.ReadLine();

            }

        }

    }

    private static string GetConnectionString()

        // To avoid storing the sourceConnection string in your code,

        // you can retrieve it from a configuration file.

    {

        return "Data Source=(local); " +

            " Integrated Security=true;" +

            "Initial Catalog=AdventureWorks;";

    }

}

Reference

SqlBulkCopy Class (System.Data.SqlClient) | Microsoft Learn