Powershell 到 SQL 数据库 [英] Powershell to SQL database

问题描述

我最初的帖子是如何将数据直接从管道传递到(远程)SQL 数据库.写入 CSV 然后数据库无法处理我正在处理的数据量 - 我正在查询超过 200 万个文件.

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.

下面是我想出的最终脚本.它扫描文件服务器，过滤管道，创建具有文件属性的 PSObject，然后将对象的属性存储到变量中.然后将这些变量传递给 SQL 查询字符串.这有点麻烦，但我看不到另一种将文件属性直接读入查询字符串的方法.

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.

推荐答案

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

这篇关于Powershell 到 SQL 数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！