Powershell to SQL database

Question

My initial post was how to pass data directly from the pipeline to a (remote) SQL database. Writing to a CSV then a database did not work for the amount of data I am working with - over 2 million files I am querying.

Below is the final script I came up with. It scans a file server, filters in the pipeline, creates a PSObject with the file attributes and then stores the attributes of the Object to variables. Those variables are then passed to the SQL query string. It's a bit cumbersome, but I could not see another way to get the file attributes read directly into the query string.

I also needed a way to run this remotely.

Answer 1

$ErrorActionPreference = "SilentlyContinue"
$cutOffDate = (Get-Date).addYears(-1)
$exclusions = @(".lnk",".url",".ini",".odc",".ctx",".upd",".ica")
$connectionString = "Server=db01;Database=Files;Integrated Security=True;"
$count = 0

$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$connection.Open()

gci "D:\USERS" -Recurse | ? { 
  $_.PSIsContainer -eq $False -and
  $_.LastAccessTime -le $cutOffDate -and
  $exclusions -notcontains $_.Extension -and
  $_.length -gt "0" -and
  $_.Directory -notmatch ".*USERS\\.*\\Personal\\sysdata\\cookies"
} | % {
  $obj = New-Object PSObject
  $obj | Add-Member NoteProperty Directory $_.DirectoryName
  $obj | Add-Member NoteProperty Name $_.Name
  $obj | Add-Member NoteProperty MB ("{0:N3}" -f ($_.Length/1MB))
  $obj | Add-Member NoteProperty LastAccessed $_.LastAccessTime
  $obj | Add-Member NoteProperty LastMofified $_.LastWriteTime
  $obj | Add-Member NoteProperty Created $_.creationtime
  $obj | Add-Member NoteProperty Extension $_.Extension

  $v1 = $obj.Directory
  $v2 = $obj.Name
  $v3 = $obj.MB
  $v4 = $obj.LastAccessed
  $v5 = $obj.LastMofified
  $v6 = $obj.Created
  $v7 = $obj.Extension
$query = "INSERT INTO dbo.fs01 (directoryPath,fName,fileSize,lastAccessed,lastModified,createdDate,extension) VALUES ('$v1','$v2','$v3','$v4','$v5','$v6','$v7');"
$command = $connection.CreateCommand()
$command.CommandText = $query
$command.ExecuteNonQuery()
}

$connection.close()