Invoke-SQLBulkCopy.ps1
|
function Invoke-SQLBulkCopy { <# .SYNOPSIS Use the .NET SQLBulkCopy class to write data to SQL Server tables. .DESCRIPTION Use the .NET SQLBulkCopy class to write data to SQL Server tables. The data source is not limited to SQL Server; any data source can be used, as long as the data can be loaded to a DataTable instance or read with a IDataReader instance. .PARAMETER ServerInstance A SQL instance to run against. For default instances, only specify the computer name: "MyComputer". For named instances, use the format "ComputerName\InstanceName". .PARAMETER Database A string specifying the name of a database. .PARAMETER Credential Specifies A PSCredential for SQL Server Authentication connection to an instance of the Database Engine. If -Credential is not specified, Invoke-Sqlcmd attempts a Windows Authentication connection using the Windows account running the PowerShell session. SECURITY NOTE: If you use the -Debug switch, the connectionstring including plain text password will be sent to the debug stream. .PARAMETER ConnectionTimeout Specifies the number of seconds when Invoke-Sqlcmd2 times out if it cannot successfully connect to an instance of the Database Engine. The timeout value must be an integer between 0 and 65534. If 0 is specified, connection attempts do not time out. .PARAMETER Force If specified, skip the confirm prompt .PARAMETER BatchSize The batch size for the bulk copy operation. .PARAMETER NotifyAfter The number of rows to fire the notification event after transferring. 0 means don't notify. Notifications hit the verbose stream (use -verbose to see them) .PARAMETER ColumnMappings A hash table with the format Key = SourceColumn, Value = DestinationColumn Example, converting SourceColumn FirstName to DestinationColumn surname, and converting SourceColumn LastName to DestinationColumn givenname @{ FirstName = 'surname' LastName = 'givenname' } .PARAMETER SQLConnection An existing SQLConnection to use .EXAMPLE Invoke-SQLBulkCopy -ServerInstance $SQLInstance -Database $Database -Table $SQLTable -DataTable $DataTable -verbose -NotifyAfter 1000 -force Insert a datatable into a table. Notify via verbose every 1000 rows. Don't prompt for confirmation. .OUTPUTS None Produces no output .NOTES This function borrows from: Chad Miller's Write-Datatable jbs534's Invoke-SQLBulkCopy Mike Shepard's Invoke-BulkCopy from SQLPSX .LINK https://github.com/RamblingCookieMonster/PowerShell .LINK http://msdn.microsoft.com/en-us/library/30c3y597 .LINK Out-DataTable .LINK Invoke-Sqlcmd2 .LINK New-SQLConnection .FUNCTIONALITY SQL #> [cmdletBinding( DefaultParameterSetName = 'Instance', SupportsShouldProcess = $true, ConfirmImpact = 'High' )] param( [parameter( Position = 0, Mandatory = $true, ValueFromPipeline = $true, ValueFromPipelineByPropertyName= $true)] [System.Data.DataTable] $DataTable, [Parameter( ParameterSetName = 'Instance', Position = 1, Mandatory = $true, ValueFromPipeline = $false, ValueFromPipelineByPropertyName = $true)] [Alias( 'SQLInstance', 'Server', 'Instance' )] [string] $ServerInstance, [Parameter( ParameterSetName = 'Connection', Position = 1, Mandatory = $true, ValueFromPipeline = $false, ValueFromPipelineByPropertyName = $false, ValueFromRemainingArguments = $false )] [Alias( 'Connection', 'Conn' )] [System.Data.SqlClient.SQLConnection] $SQLConnection, [Parameter( Position = 2, Mandatory = $true)] [string] $Database, [parameter( Position = 3, Mandatory = $true)] [string] $Table, [Parameter( ParameterSetName = 'Instance', Position = 4, Mandatory = $false)] [System.Management.Automation.PSCredential] $Credential, [Parameter( ParameterSetName = 'Instance', Position = 5, Mandatory = $false)] [Int32] $ConnectionTimeout=15, [switch] $Temp, [int] $BatchSize = 0, [int] $NotifyAfter = 0, [System.Collections.Hashtable] $ColumnMappings, [switch] $Force ) begin { #Handle existing connections if ($PSBoundParameters.Keys -contains "SQLConnection") { if ($SQLConnection.State -notlike "Open") { Try { $SQLConnection.Open() } Catch { Throw $_ } } if ($Database -and $SQLConnection.Database -notlike $Database) { Try { $SQLConnection.ChangeDatabase($Database) } Catch { Throw "Could not change Connection database '$($SQLConnection.Database)' to $Database`: $_" } } if ($SQLConnection.state -notlike "Open") { Throw "SQLConnection is not open" } } else { if ($Credential) { $ConnectionString = "Server={0};Database={1};User ID={2};Password={3};Trusted_Connection=False;Connect Timeout={4}" -f $ServerInstance,$Database,$Credential.UserName,$Credential.GetNetworkCredential().Password,$ConnectionTimeout } else { $ConnectionString = "Server={0};Database={1};Integrated Security=True;Connect Timeout={2}" -f $ServerInstance,$Database,$ConnectionTimeout } $SQLConnection = New-Object System.Data.SqlClient.SQLConnection $SQLConnection.ConnectionString = $ConnectionString Write-Debug "ConnectionString $ConnectionString" Try { $SQLConnection.Open() } Catch { Write-Error $_ continue } } $bulkCopy = New-Object System.Data.SqlClient.SqlBulkCopy $SQLConnection $bulkCopy.BatchSize = $BatchSize $bulkCopy.BulkCopyTimeout = 10000000 if ($Temp) { $bulkCopy.DestinationTableName = "#$Table" } else { $bulkCopy.DestinationTableName = $Table } if ($NotifyAfter -gt 0) { $bulkCopy.NotifyAfter=$notifyafter $bulkCopy.Add_SQlRowscopied( {Write-Verbose "$($args[1].RowsCopied) rows copied"} ) } else { $bulkCopy.NotifyAfter=$DataTable.Rows.count $bulkCopy.Add_SQlRowscopied( {Write-Verbose "$($args[1].RowsCopied) rows copied"} ) } } process { try { foreach ($column in ( $DataTable.Columns | Select -ExpandProperty ColumnName )) { if ( $PSBoundParameters.ContainsKey( 'ColumnMappings') -and $ColumnMappings.ContainsKey($column) ) { [void]$bulkCopy.ColumnMappings.Add($column,$ColumnMappings[$column]) } else { [void]$bulkCopy.ColumnMappings.Add($column,$column) } } Write-Verbose "ColumnMappings: $($bulkCopy.ColumnMappings | Format-Table -Property SourceColumn, DestinationColumn -AutoSize | Out-String)" if ($Force -or $PSCmdlet.ShouldProcess("$($DataTable.Rows.Count) rows, with BoundParameters $($PSBoundParameters | Out-String)", "SQL Bulk Copy")) { $bulkCopy.WriteToServer($DataTable) } } catch { throw $_ } } end { #Only dispose of the connection if we created it if($PSBoundParameters.Keys -notcontains 'SQLConnection') { $SQLConnection.Close() $SQLConnection.Dispose() } } } |