Dear Readers Hope you all are doing well! This is in continuation to the post https://pamwritings.blogspot.com/2026/08/medical-capital-of-india.html . Five years ago, due to many reasons, I started feeling irritation while urinating. unfortunately, it intensified further and I immediately visited a local doctor who is supposed to be an experienced doctor with MD qualification. Later on, one medical shop revealed the truth that he is not an allopathic professional and still prescribing allopathic medicines. He told me to take the tablet he prescribed for the first 2 days, can continue if required. This is the first time I listen to an advice like this and was doubtful. After taking the tablet for 2 days, infection further worsened and I started almost bleeding, a reddish color I could see. My friend near my hom...
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
Comments
Post a Comment