CODEWITHSUNDEEP
sql-serverpowershellremote-access
soMuch2Learn
soMuch2Learn

Reputation: 187

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 - 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.

Upvotes: 0

Views: 703

Answers (2)

soMuch2Learn
soMuch2Learn

Reputation: 187

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

Upvotes: 0

DarkLite1
DarkLite1

Reputation: 14695

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.

Upvotes: 2

Related Questions