Run multiple sql scripts in powershell

PowerShell Help
1

I am trying to create a PowerShell script that runs multiple scripts on a database.
here is what I have so far. It seems to only work when there is one script but when I add the second script starts to generate errors. Any help will be much appreciated.

$crlf = [System.Environment]::NewLine
[string] $TNS = 'LINKED_SERVER'
$sqlfile = "c:\temp\script1.sql", "c:\temp\script2.sql"
$connection=New-Object DATA.OracleClient.OracleConnection("Data Source=$TNS;User Id=TEST;Password=XXXXXXXX")

    foreach ($sqlfiles in $sqlfile){

    $FileLines = Get-Content $sqlfile
    $query = [string]::Join($crlf,$FileLines)
    #log start time
    [datetime]$Now = Get-Date
    [string] $TSbegin = $Now.ToString("yyyy-MM-dd HH:mm:ss")
    echo "`n`nExecuting script $sqlfiles Started at: $TSbegin"
    [System.Reflection.Assembly]::LoadWithPartialName("System.Data.OracleClient") | Out-Null
    
    $connection.Open()
    $res = (new-Object DATA.OracleClient.OracleCommand($query,$connection))
	$output = $res.ExecuteScalar()
     
    #log end time
    [string]$TSend = Get-Date -Format "yyyy-MM-dd HH:mm:ss"
    $Duration = New-TimeSpan -Start ($TSbegin) -End (Get-Date)
    echo "`n$('{0:N0}' -f $output) Rows returned"
    echo "`nExecuting script $sqlfiles completed at: $TSend"
    $Duration |Format-Table -AutoSize 
    # Remove-Variable res
   }

$connection.Close()
2

Hi Danny,

I think you’ve got your variable names mixed up (plural vs. singular).

Line 3 you create an array of file names as $sqlfile
$sqlfile = “c:\temp\script1.sql”, “c:\temp\script2.sql”

Line 6 you iterate over the $sqlfile array and each value in the array gets assigned to $sqlfiles
foreach ($sqlfiles in $sqlfile){

Line 8 you don’t use the $sqlfiles variable but $sqlfile which contains the array with all filenames. That means Get-Content will return the lines of all files into your $FileLines variable.
$FileLines = Get-Content $sqlfile

I believe it would be best if you would do the following:

Line 3: Change $sqlfile to $sqlfiles
$sqlfiles = “c:\temp\script1.sql”, “c:\temp\script2.sql”

Line 6: Swap the variable names around
foreach ($sqlfile in $sqlfiles){

Example with the suggested modifications: