Powershell To SQL Database

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 -

Solution 1:

With the help of Invoke-SQLCmd2 you can write and read data from an SQL server database. More info here.

I'm not sure but I think this CmdLet does not accept pipeline input. So your best bet would be to transform your code to a Foreach structure and invoke the Cmdlet Invoke-SQLcmd2 every time you want to do an INSERT or something else.

Something like this:

$CSV = Get-Content -Path "D:\FS01-USER-Files\$name.csv"
Foreach ($Line in $CSV) {
    Invoke-Sqlcmd2 @SQLParams -Query "
        INSERT INTO $SQLTable 
        (FileName, Data)
        VALUES('$Line.FileName', '$($Line.Data)')"
}

How can this be run with another account that has domain privileges?

You can set up a Scheduled Task that runs as another user with the password stored. This task can then be triggered by other users who have RDP access to the server where the Scheduled Task has been created.

Solution 2:

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