param
(
[string]$Path,
[string]$ConnectionString,
[string]$Table,
[string]$Delimiter = ',',
[int]$BatchSize = 75000,
[switch]$StopOnError,
[switch]$TruncateLongValues,
[switch]$NoHeaders,
[switch]$EmptyValueIsNull
)
$ErrorActionPreference = 'Stop'
$columnLengthSql = @"
select
c.[max_length]
from sys.columns c
inner join sys.objects o
on c.object_id = o.object_id and o.type = 'U'
where o.Name = N'{0}'
order by c.column_id
"@
try
{
# Connect to SQL Server
$conn = New-Object System.Data.SqlClient.SqlConnection($ConnectionString)
$conn.Open()
# Read the column widths, which we will use to check string (char type) columns
$columnWidths = Invoke-Command -NoNewScope -ScriptBlock {
try
{
$cmd = $conn.CreateCommand()
$cmd.CommandText = [string]::Format($columnLengthSql, $Table)
$cmd.CommandType = 'Text'
$rdr = $cmd.ExecuteReader()
while ($rdr.Read())
{
# Emit with into array
[int]$rdr[0]
}
}
finally
{
($rdr, $cmd) |
ForEach-Object {
$_.Dispose()
}
}
}
# Number of columns in the target table
$columnCount = $columnWidths.Count
# Set up BCP stuff
$bcpOption = ([System.Data.SqlClient.SqlBulkCopyOptions]::TableLock -bor [System.Data.SqlClient.SqlBulkCopyOptions]::UseInternalTransaction)
$bulkcopy = New-Object Data.SqlClient.SqlBulkCopy($conn, $bcpOption, $null)
$bulkcopy.DestinationTableName = $Table
$bulkcopy.bulkcopyTimeout = 0
$bulkcopy.batchsize = $BatchSize
$datatable = New-Object System.Data.DataTable
# Get the column data for the given table
# Sneakily selecting 1 row from the table puts the schema into the datatable
try
{
$sql = 'select top 1 * from [' + $Table + ']'
$dad = New-Object System.Data.SqlClient.SqlDataAdapter($sql, $conn)
[void]$dad.Fill($datatable)
}
finally
{
$dad.Dispose()
}
# If we read a row, clear it.
$datatable.Clear()
# Init row counter
$i = 0
# Headers, or not?
$headerArgument = @{}
if ($NoHeaders)
{
# If user specifies -NoHeaders, generate dummy headers - as many as there are columns
$headings = Invoke-Command -NoNewScope -ScriptBlock {
for ($i = 0; $i -lt $columnWidths.Length; $i++)
{
"Dummy$($i)"
}
}
$headerArgument = @{ Header = $headings }
}
# Let Import-Csv deal with delimiter nonsense!
Import-Csv -Path $Path -Delimiter $Delimiter @headerArgument |
ForEach-Object {
try
{
# Validate imput column count
# Import-Csv *ignores* extra columns in the input, so we will never know about them!
# Empty columns, e.g. blah,,blah are rendered as empty strings.
# If there are too few values, the remaining columns are rendered as $null (.NET null, not database null)
$populatedColumnCount = ($_.PSObject.Properties.Value | Where-Object { $_ -ne $null } | Measure-Object).Count
if ($populatedColumnCount -ne $columnCount)
{
throw "Incorrect number of columns in input. Got $($populatedColumnCount), expected $columnCount"
}
if ($TruncateLongValues -or $EmptyValueIsNull)
{
# Check columns - this will slow things down somewhat
for ($col = 0; $col -lt $datatable.Columns.Count; ++$col)
{
$inputValue = $_.PSObject.Properties.Value[$col].ToString()
if ($EmptyValueIsNull -and [string]::IsNullOrEmpty($inputValue))
{
$_."$($_.PSObject.Properties.Name[$col])" = [System.DBNull]::Value
}
elseif ($datatable.Columns[$col].DataType.FullName -eq 'System.String' -and $inputValue.Length -gt $columnWidths[$col])
{
Write-Warning "Row $($i + 1), Col $($col + 1): Value truncated"
$_."$($_.PSObject.Properties.Name[$col])" = $inputValue.Substring(0, $columnWidths[$col])
}
}
}
[void]$datatable.Rows.Add($_.PSObject.Properties.Value)
}
catch
{
# Column datatype mismatch
if ($StopOnError)
{
# Stop immediately
throw
}
# Warn the user a row didn't go in and continue
Write-Warning "Row $($i + 1): $($_.Exception.Message)"
}
if ((++$i % $BatchSize) -eq 0)
{
# Write batch
$bulkcopy.WriteToServer($datatable)
$datatable.Clear()
}
}
if ($datatable.Rows.Count -gt 0)
{
# Write remaining rows
$bulkcopy.WriteToServer($datatable)
$datatable.Clear()
}
}
catch
{
# A good script will handle exceptions here,
# e.g. failure to connect to SQL server, incorrect/un-castable datatype for column etc.
# for now, just re-throw
throw
}
finally
{
# Clean up in finally block - ensures resources are released even in event of errors.
($bulkcopy, $datatable, $conn) |
Where-Object { $_ -ne $null } |
ForEach-Object {
$_.Dispose()
}
}
I want to add a few other switches to this script. truncate table and csvlinecnt and loadcnt. I would like to clear the
table before loading, and count the number of lines to load in csv and count records loaded.
Maybe even put any output from script in an email for load tracing.
Thanks.