Getting Total Number of Rows Copied in SqlBulkCopy Using PowerShell

Getting the total number of inserted rows for SqlBulkCopy should be easier, but I believe the easiest (and fastest) is by using reflection and some C# code within your PowerShell script. Thanks to user601543 at stackoverflow, I was able translate his code for use within one of my own scripts.

rowscopied2

The Code

Here’s an approximation of how I did it. First, I added the code using Add-Type. Then I called it using [System.Data.SqlClient.SqlBulkCopyExtension]::RowsCopiedCount($bulkcopy)

# Thanks user601543 @ http://stackoverflow.com/questions/1188384/sqlbulkcopy-row-count-when-complete
Write-Host "Script started.."
$source = 'namespace System.Data.SqlClient
{    
	using Reflection;

	public static class SqlBulkCopyExtension
	{
		const String _rowsCopiedFieldName = "_rowsCopied";
		static FieldInfo _rowsCopiedField = null;

		public static int RowsCopiedCount(this SqlBulkCopy bulkCopy)
		{
			if (_rowsCopiedField == null) _rowsCopiedField = typeof(SqlBulkCopy).GetField(_rowsCopiedFieldName, BindingFlags.NonPublic | BindingFlags.GetField | BindingFlags.Instance);            
			return (int)_rowsCopiedField.GetValue(bulkCopy);
		}
	}
}
'
Add-Type -ReferencedAssemblies 'System.Data.dll' -TypeDefinition $source
$null = [Reflection.Assembly]::LoadWithPartialName("System.Data")

Write-Host "Prepping bulk copy..."
$connstring = "Data Source=$sqlserver;Integrated Security=true;Initial Catalog=$database" 
$bulkcopy = New-Object Data.SqlClient.SqlBulkCopy($connstring)

$bulkcopy.DestinationTableName = $table
$bulkcopy.bulkcopyTimeout = 0 
$bulkCopy.BatchSize = 50000
$bulkCopy.NotifyAfter = 50000
$bulkCopy.Add_SqlRowscopied({Write-Host "$($args[1].RowsCopied) rows copied" }) # Thanks for simplifying this, CookieMonster!

Write-Host "Inserting rows..."
# WriteToServer, however you want to do it.
[void]$bulkCopy.WriteToServer($cmd.ExecuteReader([System.Data.CommandBehavior]::CloseConnection))

# "Note: This count does not take into consideration the number of rows actually inserted when Ignore Duplicates is set to ON."
$total = [System.Data.SqlClient.SqlBulkCopyExtension]::RowsCopiedCount($bulkcopy)
Write-Host "$total total rows written"

As the user noted: “This count does not take into consideration the number of rows actually inserted when Ignore Duplicates is set to ON.”

Chrissy is a Cloud and Datacenter Management & Data Platform MVP who has worked in IT for over 20 years. She is the creator of the popular SQL PowerShell module dbatools, holds a master's degree in Systems Engineering and is coauthor of Learn dbatools in a Month of Lunches. Chrissy is certified in SQL Server, Linux, SharePoint and network security. You can follow her on Twitter at @cl.

Posted in PowerShell, SQL Server
2 comments on “Getting Total Number of Rows Copied in SqlBulkCopy Using PowerShell
  1. Mike Price says:

    Is there any way to get a count of the Ignored Duplicates? I’d like to be able to get this for checks-and-balances to verify the number of rows inserted plus those igonred equal the number of records on the input.

  2. Hans Tacken says:

    Hi Chrissy,

    Thanks for your nice blog. I’m using your solution above in my scripts to download data from Oracle to SQL Server. I have some tables that contain more than 2 billion rows and therefore the variable that stores the count overflows. I tried changing the int to a long but that gives an error.
    Do you have a suggestion how to fix the script so it can handle more than 2^32 rows? (I’m not a C# programmer and don’t understand exactly what the code is doing)

    Thanks,
    Hans

Leave a Reply