如何为每个SQL存储过程结果生成独立的Export-CSV输出文件？

我需要调用多个返回不同列名结果的SQL存储过程，当前已实现PowerShell脚本遍历执行各查询，但所有结果被合并到一个文本文件中。请问是否可以通过Export-CSV为每个查询的结果生成独立的输出文件？

现有脚本 #

## DB Variables
    
    $ServerName = "vLonMhDb2012-1"
    $username = "sa"
    $passwordfile = "\\vlonmhapp2012-1\APX$\auto\Scripts\APXSA_PasswordSecurestring.txt"
    $SSPassword = cat $Passwordfile | convertto-securestring
    $PSCredential = new-object -typename System.Management.Automation.PSCredential -argumentlist $username,$SSPassword                
    $DBPassword = $PSCredential.GetNetworkCredential().Password
    $DataBase = "APXFirm"
    $queries = @("set nocount on;EXEC [APXFirm].[APXUserCustom].[pReconPortfolioSFIM] @portfolios='@CUST_Recon_UBSLondon'",
    "set nocount on;EXEC [APXFirm].[APXUserCustom].[pReconSecuritySFIM] @Portfolios='@CUST_Recon_UBSLondon'",
    "set nocount on;EXEC [APXFirm].[APXUserCustom].[pReconAppraisalSFIM] @Portfolios='@CUST_Recon_UBSLondon', @DATE = '{yest}', @UseSettlementDate = 0, @AccruedInterestID = 3",
    "set nocount on;EXEC [APXFirm].[APXUserCustom].[pReconCashSFIM] @Portfolios='@CUST_Recon_UBSLondon', @DATE = '{yest}', @UseSettlementDate = 0, @AccruedInterestID = 3",
    "set nocount on;EXEC [APXFirm].[APXUserCustom].[pReconTransactionHistoryAOSEMEA_181] @IsFromDataLoad=1, @IsUseStoredAuditEventID=1, @PortfolioBaseCode='@CUST_Recon_UBSLondon', @UseSettleDate=0, @StartDate='{tdly}', @EndDate='{toda}'")

##------------------------------------------------------------------
## Run Stored Proc
##------------------------------------------------------------------

 foreach ($query in $queries)

{Invoke-SqlCmd -ServerInstance $ServerName -QueryTimeout 0 -Database $DataBase -Credential $PSCredential -Query $query -Verbose *>> "C:\temp\$CurrentTime.txt"}

尝试过的无效代码 #

{Invoke-SqlCmd -ServerInstance $ServerName -QueryTimeout 0 -Database $DataBase -Credential $PSCredential -Query $query  | Export-Csv ($Outputpath + $CurrentTime + ".csv")}

解决方案 #

核心是为每个查询生成唯一文件名，同时确保Invoke-SqlCmd的输出正确流入管道。以下是修改后的完整可运行脚本：

## DB Variables
$ServerName = "vLonMhDb2012-1"
$username = "sa"
$passwordfile = "\\vlonmhapp2012-1\APX$\auto\Scripts\APXSA_PasswordSecurestring.txt"
$SSPassword = cat $Passwordfile | convertto-securestring
$PSCredential = new-object -typename System.Management.Automation.PSCredential -argumentlist $username,$SSPassword                
$DataBase = "APXFirm"
$OutputPath = "C:\temp\"
$CurrentTime = Get-Date -Format "yyyyMMddHHmmss"

# 将查询与对应文件名标识绑定，方便生成独立文件
$queryList = @(
    @{
        Query = "set nocount on;EXEC [APXFirm].[APXUserCustom].[pReconPortfolioSFIM] @portfolios='@CUST_Recon_UBSLondon'"
        FileSuffix = "PortfolioSFIM"
    },
    @{
        Query = "set nocount on;EXEC [APXFirm].[APXUserCustom].[pReconSecuritySFIM] @Portfolios='@CUST_Recon_UBSLondon'"
        FileSuffix = "SecuritySFIM"
    },
    @{
        Query = "set nocount on;EXEC [APXFirm].[APXUserCustom].[pReconAppraisalSFIM] @Portfolios='@CUST_Recon_UBSLondon', @DATE = '{yest}', @UseSettlementDate = 0, @AccruedInterestID = 3"
        FileSuffix = "AppraisalSFIM"
    },
    @{
        Query = "set nocount on;EXEC [APXFirm].[APXUserCustom].[pReconCashSFIM] @Portfolios='@CUST_Recon_UBSLondon', @DATE = '{yest}', @UseSettlementDate = 0, @AccruedInterestID = 3"
        FileSuffix = "CashSFIM"
    },
    @{
        Query = "set nocount on;EXEC [APXFirm].[APXUserCustom].[pReconTransactionHistoryAOSEMEA_181] @IsFromDataLoad=1, @IsUseStoredAuditEventID=1, @PortfolioBaseCode='@CUST_Recon_UBSLondon', @UseSettleDate=0, @StartDate='{tdly}', @EndDate='{toda}'"
        FileSuffix = "TransactionHistoryAOSEMEA_181"
    }
)

##------------------------------------------------------------------
## Run Stored Proc & Export to Individual CSV
##------------------------------------------------------------------

foreach ($item in $queryList) {
    # 生成唯一文件名：时间戳+存储过程标识
    $csvPath = Join-Path -Path $OutputPath -ChildPath "$CurrentTime_$($item.FileSuffix).csv"
    
    # 执行查询并导出CSV，-NoTypeInformation移除冗余类型头
    Invoke-SqlCmd -ServerInstance $ServerName `
                  -QueryTimeout 0 `
                  -Database $DataBase `
                  -Credential $PSCredential `
                  -Query $item.Query | 
        Export-Csv -Path $csvPath -NoTypeInformation -Encoding UTF8
    
    # 可选：将执行日志写入单独文件
    Write-Verbose "Exported results to $csvPath" *>> "$OutputPath$CurrentTime.log"
}

关键修改说明 #

• 用哈希表数组绑定查询和文件名标识，避免手动拆分存储过程名称，降低出错概率
• 使用Join-Path拼接路径，避免因字符串拼接导致的路径格式错误
• 添加-NoTypeInformation参数，移除CSV默认的#TYPE System.Data.DataRow冗余头部
• 日志输出单独写入日志文件，与查询结果CSV分离
• 原尝试的问题并非管道无法传递，而是所有查询输出到同一个文件名导致覆盖，现在每个查询对应独立文件

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