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.

DarkLite1 · Accepted Answer · 2015-08-31 09:33:07Z

2

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.

edited Aug 31, 2015 at 9:33

answered Aug 31, 2015 at 8:58

DarkLite1

14.9k46 gold badges138 silver badges240 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user330315 Over a year ago

Does "read data from an SQL database" mean "read from any relational database using SQL as the query language"? Or do you really mean "read from a Microsoft SQL Server database"?

DarkLite1 Over a year ago

I believe it's only Microsoft SQL databases that are supported. If you need more info, please consult the link in the post. As I'm not the author of the function. But for my purposes it works really great!

user330315 Over a year ago

Then you should change "SQL database" to "SQL Server database". "SQL" is just a query language, not a DBMS product. Every relational database is a "SQL database".

soMuch2Learn · Accepted Answer · 2015-09-04 19:25:49Z

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

Collectives™ on Stack Overflow

Powershell to SQL database

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related