Wednesday, 20 March 2013

Run multiple SQL scripts and output to CSV using Powershell

I created this so I could run multiple SQL Scripts that are saved in a folder and output the results into individual CSV files.

It could be altered to simply run the SQL scripts which may be stored procedure calls etc instead fairly easily if that's your desire by removing the pipe output to CSV.

The invoke SQL line does all the work but I've put it in a for each loop so it picks up each .sql script in the set folder. I've also included logging to a file so you can see what happened and what any errors were.

When writing any code I try and not hard code anything and use variables instead, one of the first things I learnt when learning vb all those years ago. Doing that allows for the code to be reusable and easier to adjust.

All you need to do to use this is adjust the variables at the top, you could also set those so you can call the script remotely providing the values.

I created this only the other day so I'm sure I'll update it, if I add anything flash I'll make sure to share.

# Running this locally you may need to run the two below lines first to set up SQL snap ins 
# add-pssnapin sqlserverprovidersnapin100 
# add-pssnapin sqlservercmdletsnapin100 


$csvOutputFilePath = "C:\Scripts" #Set Output File Path 
$sqlScriptsFilePath = "C:\Scripts" #Set Location of Scripts 
$LogFilePath = "C:\Scripts" #Set Location of Logs 
$Server = "YourServerName" #Set Database Server 
$Database = "YourDatabaseName" #Set Database to use 
$QueryTimeout = 1800 

$LogFileName= "RunScriptsLog_" + (get-date -f yyyy-MM-dd-HH-mm-ss-ms) + ".Log" #Set up the Logfile name to use 
$Script:LogFile = "$LogFilePath\$LogFileName" #Setup the Path for the log with the filename 

#Add Header info to Log file 
add-content $LogFile "SQL Script Execution" 
add-content $LogFile "BeginTime: $BeginTime" 
add-content $LogFile "CompleteTime: $CompleteTime" 
add-content $LogFile "Server: $Server" 
add-content $LogFile "Database: $Database" 
add-content $LogFile "CSV Output File Path: $csvOutputFilePath" 
add-content $LogFile "SQL Scripts File Path: $sqlScriptsFilePath" 
add-content $LogFile "Log File Path: $LogFilePath" 
add-content $LogFile "Query Timeout: $QueryTimeout" 
add-content $LogFile "" 

#Go find the scripts to run 
#We are only interested in files that end with .sql 
$instanceNameList = Get-ChildItem $sqlScriptsFilePath -filter "*.sql" 
  
  
#For Each Script file found in the Script Folder run the below 
 foreach($instanceName in $instanceNameList) 

 {#We use a try so we can catch any errors 
    try 
        { 
            $BeginTime = get-date #Record Time Script Started 
            $OutPutFileName = "$instanceName" #Set the Out put file name to the name of the Script 
            #Then we manipulate the Output File name a little to 
                #Remove .sql from the name 
                #add a timestamp 
                #add the csv extension 
            $OutPutFileName = $Database + " - " + $OutPutFileName.Replace(".sql", "") + "_" + (get-date -f yyyy-MM-dd-HH-mm-ss-ms) + ".csv" 
            write-host "Executing query against server: " $instanceName "OutPutFileName: " $OutPutFileName #Output to screen what script is about to be run and what file it will create 
            #Run the Script on the set Server/Database and output it to the file 
            Invoke-Sqlcmd -ServerInstance $Server -Database $Database -QueryTimeout $QueryTimeout -InputFile "$sqlScriptsFilePath\$instanceName" | Export-Csv -Path "$csvOutputFilePath\$OutPutFileName" -NoTypeInformation 
            #Add details to the Log file on what happened including the Begin and Complete Times. 
            $CompleteTime = get-date 
            add-content $LogFile "Script Execution - BeginTime: $BeginTime. CompleteTime: $CompleteTime. Script: $instanceName. OutPutFileName: $OutPutFileName $_" 
        } 
        
    catch #If there is an error we catch it here and output the error to the screen and to the Log File 
    { 
    $CompleteTime = get-date 
    write-host "**Error** running Script: $instanceName. BeginTime: $BeginTime. CompleteTime: $CompleteTime. 
**Error** $_" #$_ appends the output which in this case should be the error message 
    add-content $LogFile "**Error** running Script: $instanceName BeginTime: $BeginTime. CompleteTime: $CompleteTime. 
**Error** $_" 
    } 
}