Add switch statements to script

[AndyH45 Participant Topics: 2 Replies: 8 Points: 30Rank: Member]

 

PowerShell

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() } }

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187

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.

[kvprasoon Senior Moderator Topics: 9 Replies: 1449 Points: 5,426Rank: Community MVP]

Are you the author of this script ? let us know little more on what problem are you facing here.

[AndyH45 Participant Topics: 2 Replies: 8 Points: 30Rank: Member]

This was a script I asked for some help creating, as I had something similar, but it wasn’t doing the switch’s in the original design. I would like to add more functionality of record counts both of the csv and “what loaded to SQL”. Then I thought
adding a TruncateTable since I would always be loading a Staging table.

Thanks.

[AndyH45 Participant Topics: 2 Replies: 8 Points: 30Rank: Member]

Any help in adding a few new options to this script?

 

Thanks

[Mike R. Participant Topics: 5 Replies: 253 Points: 1,007Rank: Community Hero]

Sounds like when you say “switch statements” you mean switch parameters.  As your script is now, I don’t see any switch statements.  See Get-Help about_switch

To add switch parameters, just put them in the param block and reference them in the script where appropriate.  These will be simply True of False depending on if the caller included the switch, so you can use a simple if statement like you see in line 90 and 122 of your current posted script.

Recommend you refine your new requirements some more and try to implement the code as much as you can, then maybe someone can help you with logic/errors as needed.  Right now what you are trying to accomplish seems a little vague to me.

[AndyH45 Participant Topics: 2 Replies: 8 Points: 30Rank: Member]

One of the requirements was to Truncate Table, the other where to verify records in csv match to what was loaded.
If not send alert of the records loaded didn’t match records received.

 

 

[Author]

Posts