Unable to export the data returned from Azure Stored Procedure to CSV in Azure Automation Powershell

Question

I am pretty new to Azure Automation PowerShell as well as to Azure SQL Stored procedure. The requirement is to trigger a Stored procedure (Simple Select command) from the Azure Automation PowerShell job and save the data returned to CSV and then zip the CSV and send a email with zipped attachment.

I am struggling to ensure if the data is being returned from the stored proc to Automation job. Stored Proc when run in SSMS its executing and returning data. But in Automation job I am not sure as when i export it to CSV and try zipping.. The file doesn't get generated. I don't get any error in console as well.

Stored Procedure:`Stored Procedure

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER Procedure [SISWEB_OWNER].[LOC_REPORT] 
    @LogMessage VARCHAR(100)= null
as
Begin

--Start
DECLARE @ProcName VARCHAR(200)
SET @ProcName= OBJECT_SCHEMA_NAME(@@PROCID)+'.'+OBJECT_NAME(@@PROCID) 

EXEC SISSEARCH.WriteLog @NAMEOFSPROC = @PROCNAME,@LOGMESSAGE = 'Execution started';

SELECT CONCAT(MEDIANUMBER, ' ', CAST(REVISIONNO AS int)) AS MEDIANUMBER,
MEDIATITLE,
PUBLISHEDDATE,
MEDIAUPDATEDDATE
FROM SISWEB_OWNER.MASMEDIA
WHERE 
MEDIAUPDATEDDATE >= DATEADD(MONTH, -1, GETDATE())
AND MEDIANUMBER IN ( 
SELECT MEDIANUMBER 
FROM SISWEB_OWNER.LNKMEDIASNP LMS, SISWEB_OWNER.LNKPRODUCT LP 
WHERE LMS.SNP = LP.SNP 
AND LP.PRODUCTCODE NOT IN ('ONHT', 'EMP')
)

RETURN

--End
EXEC SISSEARCH.WriteLog @NAMEOFSPROC = @PROCNAME,@LOGMESSAGE = 'Execution completed';

End

Azure Automation PowerShell script Azure Automation PowerShell script

workflow LocReport
{

    $sqlServerName = Get-AutomationVariable -Name 'SQLServerName'
    $databaseCredentialName = Get-AutomationVariable -Name 'DatabaseCredentialName'
    $databaseCredential = Get-AutomationPSCredential -Name $databaseCredentialName

    # Create Folder if not exist already
    Write-Output "Creates Folder if not exist already"
    $path = "$Env:temp/LocReport"
    If(!(test-path $path))
    {
      New-Item -ItemType Directory -Force -Path $path
    }
    Write-Output "Folder creation completed"

    # Call stored proc to run the LocReport
    Write-Output "Call SQL_Agent_SprocJob for LOC_REPORT"
    Write-Output "SQL execution started..."
    SQL_Agent_SprocJob -SqlServerName $sqlServerName -DBName "sis" -StoredProcName "SISWEB_OWNER.LOC_REPORT" -Credential $databaseCredential 
    Write-Output "SQL execution completed..."

    # Exporting the report data to CSV.
    Write-Output "Exporting to CSV started..."
    $LocReport | Export-Csv "$Env:temp/LocReport/Report.csv"    
    Write-Output "Exporting to CSV completed..."

    # Zip the CSV
    Write-Output "Creating zip file"
    $CreateZip = Compress-Archive -Path "$path" -DestinationPath "$path/ErinReport.zip" -CompressionLevel Optimal -Force

    Test-path $path/*

    $sendGridCredentialName = Get-AutomationVariable -Name 'SendGridCredentialName'
    $sendGridCredential = Get-AutomationPSCredential -Name $sendGridCredentialName

    # Call SendEmail Automation job for sending the LocReport
    Write-Output $LocReport
    Write-Output "Sending Email"
    Send-MailMessage -From "*********" -Subject "Loc Report" -Body "LOC Report CSV File" -Attachments "$path/ErinReport.zip" -To "*******" -SmtpServer "smtp.sendgrid.net" -Port 587 -Credential $sendGridCredential
    Write-Output "Email Sent"
    
}

I get the following error while running the Azure Automation Error Message in Automation job`` Please help resolve this issue and how to save the data returned from Stored proc in PowerShell