out-sql Powershell function - export pipeline contents to a new SQL Server table
Recently I needed to take output of command-line tool and cross-reference it with information in the database. The tool's output was XML, and I was additionally processing it a bit with Powershell. Now, how to get that to SQL Server?
I searched for out-sql Powershell cmdlet or function which allows to save the pipeline data to SQL Server, and I could not find one. So I wrote one.
It did the job for me, but obviously it's not perfect. Specifically:
- I use plain old INSERT statements - it's not the fastest method for inserting large amounts of data. Furthermore, I send each individual statement as a batch and an individual transaction. But I would not worry about it unless I insert hundreds of thousands or rows.
- No explicit error handling. SQL will throw the errors if it has a problem. The drawback is that script execution won't stop on say connection open error, and will continue - printing an additional error for every row.
- Short of dates, I save everything as NVARCHAR(MAX). I chose not to mess with integers - it was not needed for my project.
If you do end up using this, drop me a note on your experiences. If you do make enhancements, feel free to share with me!
Here's the function:
##############################################################################
##
## out-sql.ps1
##
## by Alexey Yeltsov, Microsoft Corp.
##
## Export pipeline contents into a new SQL table
##
## Parameters:
## $SqlServer - SQL Server
## $Database - Database name
## $Table - Table name
## $DropExisting - Drop $Table if it already exists and recreate it
## (default $false)
## $RowId - Add identity column named $RowId and make it a primary key.
## (default "RowID". Can pass $null if identity is not needed)
##
##
## Examples:
## ## #First, load the function
## . .\out-sql.ps1
##
## #Export processes to table Process in database Scratch on local sql server
## get-process | out-sql -SqlServer . -database Scratch -table Process -dropexisting $true
##
## #Export volume details from 4 servers into a table
## @("Server1","Server2","Server3","Server4") `
## | % {$Server = $_ ; Get-WMIObject Win32_Volume -computer $Server } `
## | Select-Object `
## SystemName, `
## Name, `
## @{Name="CapacityGb";Expression={[math]::truncate($_.Capacity / 1Gb)}}, `
## @{Name="FreeGb";Expression={[math]::truncate($_.FreeSpace / 1Gb)}} `
## | out-sql -sqlserver . -database Scratch -table DiskVolume -dropexisting $true
##
##
##
############################################################################## function Out-Sql($SqlServer=$null,$Database=$null,$Table=$null,$DropExisting=$false,$RowId="RowID") { begin { $Line = 0 [string]$CreateTable = "" if(-not $SqlServer) { throw 'Out-Sql expects $SqlServer parameter' } if(-not $Database) { throw 'Out-Sql expects $Database parameter' } if(-not $Table) { throw 'Out-Sql expects $Table parameter' } if($DropExisting) { write-debug "Note: If the table exists, it WILL be dropped and re-created."} $SqlConnectionString = " Provider=sqloledb;" + " Data Source=$SqlServer;" + " Initial Catalog=$Database;" + " Integrated Security=SSPI;" write-debug "Will open connection to SQL server ""$SqlServer"" and will populate table ""$Table.""" write-debug "Connection string: `n$SqlConnectionString" $SqlConnection = New-Object System.Data.OleDb.OleDbConnection $SqlConnectionString $SqlCommand = New-Object System.Data.OleDb.OleDbCommand "",$SqlConnection $SqlConnection.Open() } process { $Line ++ $Properties = $_.PSObject.Properties if (-not $Properties) { throw "Out-Sql expects object to be passed on the pipeline. The object must have .PSObject.Properties collection." } #if we're at the first line, initialize the table if ($Line -eq 1) { #initialize SQL connection and create table if($DropExisting) { $CreateTable += "IF OBJECT_ID('$Table') IS NOT NULL DROP TABLE $Table;`n"} $CreateTable +="CREATE TABLE $Table ( `n" $col = 0 if ($RowId) { $col++; $CreateTable +="$RowId INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED `n" } foreach($Property in $Properties) { $col++; if ($col -gt 1) { $CreateTable +="," } # In below, why not use "if ($Property.Value -is [datetime])"? # Because access can be denied to the value, but Property.TypeNameOfValue would still be accessible! if ($Property.TypeNameOfValue -eq "System.DateTime") { $CreateTable +="$($Property.Name) DATETIME NULL `n" } else { $CreateTable +="$($Property.Name) NVARCHAR(MAX) NULL `n" } } $CreateTable +=")" write-debug "Will execute SQL to create table: `n$CreateTable" $SqlCommand.CommandText = $CreateTable $rows = $SqlCommand.ExecuteNonQuery() } #Prepare SQL insert statement and execute it $InsertStatement = "INSERT $Table VALUES(" $col = 0 foreach($Property in $Properties) { $col++; if ($col -gt 1) { $InsertStatement += "," } #In the INSERT statement, do speacial tratment for Nulls, Dates and XML. Other special cases can be added as needed. if (-not $Property.Value) { $InsertStatement += "null `n" } elseif ($Property.Value -is [datetime]) { $InsertStatement += "'" + $Property.Value.ToString("yyyy-MM-dd HH:mm:ss.fff") + "'`n" } elseif ($Property.Value -is [System.Xml.XmlNode] -or $Property.Value -is [System.Xml.XmlElement]) { $InsertStatement += "'" + ([string]$($Property.Value.Get_OuterXml())).Replace("'","''") + "'`n" } else { $InsertStatement += "'" + ([string]$($Property.Value)).Replace("'","''") + "'`n" } } $InsertStatement +=")" write-debug "Running insert statement: `n $InsertStatement" $SqlCommand.CommandText = $InsertStatement $rows = $SqlCommand.ExecuteNonQuery() } end { write-debug "closing SQL connection..." $SqlConnection.Close() }
}
Enjoy,
Alexey
.jpg)
No comments:
Post a Comment