Azure Automation PowerShell中无法导出Azure SQL存储过程返回数据至CSV的问题排查及数据保存方法咨询

咱们一步步拆解你的问题，从存储过程到PowerShell脚本逐一排查修复：

1. 存储过程的小瑕疵（不影响数据返回，但影响日志完整性） #

你的存储过程里SELECT之后直接用了RETURN，这会导致后续的执行完成日志不会触发。把RETURN移到最后就能解决：

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')
)

--End
EXEC SISSEARCH.WriteLog @NAMEOFSPROC = @PROCNAME,@LOGMESSAGE = 'Execution completed';
RETURN -- 移到最后，确保日志执行
End

2. 核心问题：确保存储过程结果能传递到PowerShell #

你用的自定义SQL_Agent_SprocJob函数是关键变量——如果它没有正确捕获并返回存储过程的结果集，$LocReport就会是空值，后续导出CSV自然无法生成有效文件。

替换为标准的Invoke-SqlCmd（更可靠） #

建议改用Azure Automation原生支持的Invoke-SqlCmd命令（需确保你的Automation账户已导入SqlServer模块），能明确获取结构化结果：

# 替换原有的SQL调用逻辑
$connectionString = "Server=tcp:$sqlServerName,1433;Database=sis;User ID=$($databaseCredential.UserName);Password=$($databaseCredential.GetNetworkCredential().Password);Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"
$LocReport = Invoke-SqlCmd -ConnectionString $connectionString -Query "EXEC SISWEB_OWNER.LOC_REPORT" -OutputAs DataTables

-OutputAs DataTables参数确保返回的数据能直接用于Export-Csv。

3. 修复文件生成与压缩问题 #

增强日志排查 #

添加详细日志确认每一步的执行结果，帮你定位问题：

# 导出CSV后验证
Write-Output "返回数据行数：$($LocReport.Count)"
Write-Output "CSV路径：$Env:temp/LocReport/Report.csv"
Write-Output "CSV是否存在：$(Test-Path "$Env:temp/LocReport/Report.csv")"
Write-Output "CSV文件大小：$(if (Test-Path "$Env:temp/LocReport/Report.csv") { (Get-Item "$Env:temp/LocReport/Report.csv").Length } else { '0' })"

# 压缩后验证
Write-Output "Zip文件是否存在：$(Test-Path "$path/ErinReport.zip")"
Write-Output "Zip文件大小：$(if (Test-Path "$path/ErinReport.zip") { (Get-Item "$path/ErinReport.zip").Length } else { '0' })"

压缩命令优化 #

你的Compress-Archive是压缩整个文件夹，如果你只想打包单个CSV文件，可以调整为：

Compress-Archive -Path "$path/Report.csv" -DestinationPath "$path/ErinReport.zip" -CompressionLevel Optimal -Force

4. 避开Azure Automation Workflow的坑 #

Workflow是旧技术，存在会话隔离、cmdlet兼容性等限制，建议改用普通PowerShell Runbook（去掉workflow关键字），行为更贴近本地PowerShell，减少意外问题。

5. 完整修改后的脚本示例 #

# 普通PowerShell Runbook（非Workflow）
$sqlServerName = Get-AutomationVariable -Name 'SQLServerName'
$databaseCredentialName = Get-AutomationVariable -Name 'DatabaseCredentialName'
$databaseCredential = Get-AutomationPSCredential -Name $databaseCredentialName

# 创建文件夹（若不存在）
Write-Output "检查并创建输出文件夹..."
$path = "$Env:temp/LocReport"
If(!(test-path $path)) {
    $null = New-Item -ItemType Directory -Force -Path $path
    Write-Output "文件夹已创建：$path"
} else {
    Write-Output "文件夹已存在：$path"
}

# 执行存储过程获取数据
Write-Output "开始执行存储过程..."
$connectionString = "Server=tcp:$sqlServerName,1433;Database=sis;User ID=$($databaseCredential.UserName);Password=$($databaseCredential.GetNetworkCredential().Password);Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"
try {
    $LocReport = Invoke-SqlCmd -ConnectionString $connectionString -Query "EXEC SISWEB_OWNER.LOC_REPORT" -OutputAs DataTables
    Write-Output "存储过程执行成功，返回$($LocReport.Count)行数据"
} catch {
    Write-Error "存储过程执行失败：$_"
    throw
}

# 导出数据到CSV
Write-Output "开始导出CSV..."
$csvPath = "$path/Report.csv"
try {
    $LocReport | Export-Csv -Path $csvPath -NoTypeInformation -Force
    Write-Output "CSV导出成功，路径：$csvPath"
    Write-Output "CSV文件大小：$(Get-Item $csvPath).Length 字节"
} catch {
    Write-Error "CSV导出失败：$_"
    throw
}

# 压缩CSV文件
Write-Output "开始创建压缩包..."
$zipPath = "$path/ErinReport.zip"
try {
    Compress-Archive -Path $csvPath -DestinationPath $zipPath -CompressionLevel Optimal -Force
    Write-Output "压缩包创建成功，路径：$zipPath"
    Write-Output "压缩包大小：$(Get-Item $zipPath).Length 字节"
} catch {
    Write-Error "压缩包创建失败：$_"
    throw
}

# 发送邮件
$sendGridCredentialName = Get-AutomationVariable -Name 'SendGridCredentialName'
$sendGridCredential = Get-AutomationPSCredential -Name $sendGridCredentialName

Write-Output "开始发送邮件..."
try {
    Send-MailMessage -From "你的邮箱地址" -Subject "Loc Report" -Body "LOC Report CSV文件已附在邮件中" -Attachments $zipPath -To "收件人邮箱地址" -SmtpServer "smtp.sendgrid.net" -Port 587 -Credential $sendGridCredential -UseSsl
    Write-Output "邮件发送成功"
} catch {
    Write-Error "邮件发送失败：$_"
    throw
}

6. 额外排查步骤 #

• 检查Automation模块：确保SqlServer模块已导入到你的Automation账户（在“Modules”选项中添加）。
• 查看完整作业日志：在Azure门户的Automation作业页面，切换到“所有日志”标签，能看到详细的报错信息（之前你提到的错误消息未提供，这里可以找到）。
• 测试小数据量：先修改存储过程返回少量数据，确认脚本能正常生成文件和发送邮件。

内容的提问来源于stack exchange，提问作者Rupa