0

I have a powershell script that makes an API Call and passes the data to a MS SQL Server database. I have created a batch file to execute the powershell script I have tested the powershell script and the batch file and everything works as expected. however, when i create a scheduled task to execute the batch file, the powershell script runs however does not make the API Call. Because the batch file executes successfully task scheduler returns a completed message.

My Powershell script

$FilePath = "I:\API"
$LogFilePath = "$FilePath\API.log"
$Now = Get-Date

#Write-Output  "$Now $server $database $api Entry" | Out-File $LogFilePath -Append


$server  = "SERVER"
$database = "DB"
$apikey = "APIKEY"



###FUNCTION FOR INSERTING INTO SQL###

Function Add-APIData ($server, $database, $text)
{
    $scon = New-Object System.Data.SqlClient.SqlConnection
    
    $scon.ConnectionString = "SERVER=$server;DATABASE=$database;Integrated Security=false; UID=UID; Password = PWD"
    


    $cmd = New-Object System.Data.SqlClient.SqlCommand
    $cmd.Connection = $scon
    $cmd.CommandText = $text
    $cmd.CommandTimeout = 0

    $scon.Open()
    $cmd.ExecuteNonQuery()
    $scon.Close()
    $cmd.Dispose()
    $scon.Dispose()
}

###MAKING THE API CALL###

$ErrorMessage= $Error[0].Exception.Message
    if($ErrorMessage -ne $null) {
     

Write-Output  "$Now Initiating API Call" | Out-File $LogFilePath -Append


[string]$webstring = "APIENDPOINT"
Write-Host $webstring
$webget = New-Object System.Net.WebClient

#Write-Host $webstring

$webget.Proxy.Credentials = [System.Net.CredentialCache]::DefaultNetworkCredentials
$result = $webget.DownloadString($webstring)

#Write-Host $result


$result = ConvertFrom-Json $result

$add = @()

##Write-Host $result 



foreach ($r in $result.data)
{
        $add += "INSERT INTO Schema.Table  (COL1,COL2,COL3)  VALUES ('" + $r.Response1 + "','" +    $r.Response2 + "','" +  $r.Response3 +
 "')" + $nl
}


Write-Output  "$Now Initiating DB insert" | Out-File $LogFilePath -Append
Out-File -FilePath $FilePath\InsertSQL.txt -InputObject $add
Write-Output  "$Now API Call Complete. Data written to DB" | Out-File $LogFilePath -Append


Try {
Add-APIData -server $server -database $database -text $add
}

Catch

{
Write-Warning "Error message: $_"
            if ( $errorlog ) {
                $errormsg = $_.ToString()
                $exception = $_.Exception
                $stacktrace = $_.ScriptStackTrace
                $failingline = $_.InvocationInfo.Line
                $positionmsg = $_.InvocationInfo.PositionMessage
                $pscommandpath = $_.InvocationInfo.PSCommandPath
                $failinglinenumber = $_.InvocationInfo.ScriptLineNumber
                $scriptname = $_.InvocationInfo.ScriptName
                Write-Verbose "Start writing to Error log."
                Write-Output "$errormsg" | Out-File $LogFilePath -Append
                Write-Verbose "Finish writing to Error log."
}
}
}


Write-Output  "$Now  TEST $ErrorMessage" | Out-File $LogFilePath -Append

exit

I've created some rudementary logging to see if i get any responses and from waht i can tell is the only line executing in the powershell script is

Write-Output  "$Now  TEST $ErrorMessage" | Out-File $LogFilePath -Append

Which returns a result without any error message

12/02/2020 15:00:01 TEST

The batch file is written as

powershell.exe "& "" <PowershellScriptFilepath>.ps1"""
exit

Task Scheduler is setup as such: Action -> Start a Program -> .bat

Can someone please explain to me why task scheduler is not executing the batch/powershell scripts as expected?

wz2k15
  • 3
  • 2
  • When debugging Powershell in a scheduled task, it helps to have a [transcript](https://learn.microsoft.com/en-us/powershell/module/microsoft.powershell.host/start-transcript?view=powershell-7.1). Don't forget to `Stop-Transcript` at the end of your script. – Rich Moss Dec 02 '20 at 17:26
  • Thanks, I have added this and am getting results when execute the batch file, however the API Call still doesnt execute. – wz2k15 Dec 03 '20 at 10:27
  • Have you tried running the script outside Windows Task Scheduler, at a Powershell command line? If it behaves correctly, it might be that the user account the Scheduled Task is running under doesn't have permission to execute the `INSERT INTO Schema.Table` SQL script. If it doesn't work at the command line, try debugging the script with Powershell ISE. You can set break points (F9) where you think it's failing, start debugging (F5) and examine variables, then single-step (F11) through the code. – Rich Moss Dec 03 '20 at 18:53
  • Note that the ISE, while it's installed on most systems, is considered [obsolete](https://stackoverflow.com/questions/57131654/using-utf-8-encoding-chcp-65001-in-command-prompt-windows-powershell-window/57134096#57134096) (see "Optional Reading" section) – Rich Moss Dec 03 '20 at 19:02

0 Answers0