Power shell - save sql query result to file

 1. Simple script to run query and save it to file

$fileName = 'FILE_PATH'

$SQLServer = "SERVER_NAME"  

$SQLDBName = "DATABASE_NAME"  

$userid ="USER_NAME"  

$password = "PASSWORD"   

$delimiter = ";"

$SqlQuery = "SELECT * FROM TABLE_NAME";

#SQL Query 

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection  

$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; User ID = $userid; Password = $password;"

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand  

$SqlCmd.CommandText = $SqlQuery  

$SqlCmd.Connection = $SqlConnection  

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter  

$SqlAdapter.SelectCommand = $SqlCmd   

#Dataset  and save to file

$DataSet = New-Object System.Data.DataSet  

$SqlAdapter.Fill($DataSet)  

$DataSet.Tables[0] | export-csv -Delimiter $delimiter -Path $fileName -NoTypeInformation

Powershell - upload file to ftp

 1. Simple script for uploading files to ftp

$username = "FTP_USER"

$password = "FTP_PASSWORD"

$localFile = "LOCAL_FILE_PATH"

$remoteFile = "ftp://SERVER_ADDRESS/" + "FILENAME"

# Create FTP Rquest

$request = [System.Net.FtpWebRequest]::Create("$remoteFile")

$request = [System.Net.FtpWebRequest]$request

$request.Method = [System.Net.WebRequestMethods+Ftp]::UploadFile

$request.Credentials = new-object System.Net.NetworkCredential($username, $password)

$request.UseBinary = $true

$request.UsePassive = $true

# Read the File

$fileContent = gc -en byte $localFile

$request.ContentLength = $fileContent.Length

$run = $request.GetRequestStream()

$run.Write($fileContent, 0, $fileContent.Length)

# Close and dispose connection

$run.Close()

$run.Dispose()

Power Shell: Remove files, subfolders older than X days

Power Shell: Remove files, subfolders older than X days

1. Power shell script to remove files and subfolders older than 30 days from directory "E:\directory\

Get-ChildItem -path "E:\directory\" -recurse | where-object {$_.LastWriteTime -lt (Get-Date).AddDays(-30)} | Remove-Item -Recurse -Force

2. Schedule script: E:\RemoveFiles.ps1

Action: Start a program

Program: PowerShell.exe

Add arguments (optiona): -ExecutionPolicy Bypass E:\RemoveFiles.ps1