Saturday, October 4, 2008

out-sql Powershell function - export pipeline contents to a new SQL Server table

 

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




Random thoughts by SQL DBA/System Administrator : out-sql Powershell function - export pipeline contents to a new SQL Server table

No comments:

Blog Archive