Getting Total Number of Rows Copied in SqlBulkCopy Using PowerShell

May 18, 2015 · 2 min read  ·
Share on:

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)

 1# Thanks user601543 @ https://stackoverflow.com/questions/1188384/sqlbulkcopy-row-count-when-complete
 2Write-Host "Script started.."
 3$source = 'namespace System.Data.SqlClient
 4{    
 5	using Reflection;
 6
 7	public static class SqlBulkCopyExtension
 8	{
 9		const String _rowsCopiedFieldName = "_rowsCopied";
10		static FieldInfo _rowsCopiedField = null;
11
12		public static int RowsCopiedCount(this SqlBulkCopy bulkCopy)
13		{
14			if (_rowsCopiedField == null) _rowsCopiedField = typeof(SqlBulkCopy).GetField(_rowsCopiedFieldName, BindingFlags.NonPublic | BindingFlags.GetField | BindingFlags.Instance);            
15			return (int)_rowsCopiedField.GetValue(bulkCopy);
16		}
17	}
18}
19'
20Add-Type -ReferencedAssemblies 'System.Data.dll' -TypeDefinition $source
21$null = [Reflection.Assembly]::LoadWithPartialName("System.Data")
22
23Write-Host "Prepping bulk copy..."
24$connstring = "Data Source=$sqlserver;Integrated Security=true;Initial Catalog=$database" 
25$bulkcopy = New-Object Data.SqlClient.SqlBulkCopy($connstring)
26
27$bulkcopy.DestinationTableName = $table
28$bulkcopy.bulkcopyTimeout = 0 
29$bulkCopy.BatchSize = 50000
30$bulkCopy.NotifyAfter = 50000
31$bulkCopy.Add_SqlRowscopied({Write-Host "$($args[1].RowsCopied) rows copied" }) # Thanks for simplifying this, CookieMonster!
32
33Write-Host "Inserting rows..."
34# WriteToServer, however you want to do it.
35[void]$bulkCopy.WriteToServer($cmd.ExecuteReader([System.Data.CommandBehavior]::CloseConnection))
36
37# "Note: This count does not take into consideration the number of rows actually inserted when Ignore Duplicates is set to ON."
38$total = [System.Data.SqlClient.SqlBulkCopyExtension]::RowsCopiedCount($bulkcopy)
39Write-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."