SSRS 和 PowerShell：获取 Excel 格式的报告

我正在尝试让 PowerShell 向我们的 SSRS 服务器发送 Web 请求并捕获结果。我已经使用了撞墙rs:FORMAT=EXCELSSRS url 字符串中的参数。我有以下内容：

首先，初始化凭据：

$User = "MYDOMAIN\MyUser"
$PWord = ConvertTo-SecureString -String "WooHooStringP$W0rd" -AsPlainText -Force
$c = New-Object –TypeName System.Management.Automation.PSCredential –ArgumentList $User, $PWord

现在，请求一份报告：

Invoke-WebRequest `
-UserAgent ([Microsoft.PowerShell.Commands.PSUserAgent]::InternetExplorer) `
-Credential $c `
-Uri "http://myserver/ReportServer_DEV/Pages/ReportViewer.aspx?/folder+path/report+name"

这很好用。我什至可以获取结果（附上此请求并使用 ().Content）。 然后，指定一种格式而不是纯渲染：

Invoke-WebRequest `
-UserAgent ([Microsoft.PowerShell.Commands.PSUserAgent]::InternetExplorer) `
-Credential $c `
-Uri "http://myserver/ReportServer_DEV/Pages/ReportViewer.aspx?/folder+path/report+name&rs:format=HTML4.0"

请注意rs:Format规格？奇迹般有效。

然后，为大结局，给我一个 Excel 文件：

Invoke-WebRequest `
-UserAgent ([Microsoft.PowerShell.Commands.PSUserAgent]::InternetExplorer) `
-Credential $c `
-Uri "http://myserver/ReportServer_DEV/Pages/ReportViewer.aspx?/folder+path/report+name&rs:format=EXCEL"

没办法，伙计：

Invoke-WebRequest : The remote server returned an error: (401) Unauthorized.
At line:1 char:11
+ $bytez = (Invoke-WebRequest `
+           ~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (System.Net.HttpWebRequest:HttpWebRequest) [Invoke-WebRequest], WebException
    + FullyQualifiedErrorId : WebCmdletWebResponseException,Microsoft.PowerShell.Commands.InvokeWebRequestCommand

为什么rs:format=EXCEL选项抛出未经授权的异常，而所有其他 URL 均由 SSRS 提供服务？

我已经想通了！我以错误的方式解决了这个问题：SSRS 通过 Web 服务提供访问，PowerShell 可以使用该服务，而无需破解 URL 并捕获响应。我找到了一个执行此操作的脚本并对其进行了修改以满足我的目的：

function GetRSConnection($server, $instance)
{
    #   Create a proxy to the SSRS server and give it the namespace of 'RS' to use for
    #   instantiating objects later.  This class will also be used to create a report
    #   object.

    $User = "DOMAIN\Username"
    $PWord = ConvertTo-SecureString -String "Pa$$w0rd" -AsPlainText -Force
    $c = New-Object –TypeName System.Management.Automation.PSCredential –ArgumentList $User, $PWord

    $reportServerURI = "http://" + $server + "/" + $instance + "/ReportExecution2005.asmx?WSDL"

    $RS = New-WebServiceProxy -Class 'RS' -NameSpace 'RS' -Uri $reportServerURI -Credential $c
    $RS.Url = $reportServerURI
    return $RS
}

function GetReport($RS, $reportPath)
{
    #   Next we need to load the report. Since Powershell cannot pass a null string
    #   (it instead just passses ""), we have to use GetMethod / Invoke to call the
    #   function that returns the report object.  This will load the report in the
    #   report server object, as well as create a report object that can be used to
    #   discover information about the report.  It's not used in this code, but it can
    #   be used to discover information about what parameters are needed to execute
    #   the report.
    $reportPath = "/" + $reportPath
    $Report = $RS.GetType().GetMethod("LoadReport").Invoke($RS, @($reportPath, $null))

    # initialise empty parameter holder
    $parameters = @()
    $RS.SetExecutionParameters($parameters, "nl-nl") > $null
    return $report
}

function AddParameter($params, $name, $val)
{
    $par = New-Object RS.ParameterValue
    $par.Name = $name
    $par.Value = $val
    $params += $par
    return ,$params
}

function GetReportInFormat($RS, $report, $params, $outputpath, $format)
{
    #   Set up some variables to hold referenced results from Render
    $deviceInfo = "<DeviceInfo><NoHeader>True</NoHeader></DeviceInfo>"
    $extension = ""
    $mimeType = ""
    $encoding = ""
    $warnings = $null
    $streamIDs = $null

    #   Report parameters are handled by creating an array of ParameterValue objects.
    #   Add the parameter array to the service.  Note that this returns some
    #   information about the report that is about to be executed.
    #   $RS.SetExecutionParameters($parameters, "en-us") > $null
    $RS.SetExecutionParameters($params, "nl-nl") > $null

    #    Render the report to a byte array.  The first argument is the report format.
    #    The formats I've tested are: PDF, XML, CSV, WORD (.doc), EXCEL (.xls),
    #    IMAGE (.tif), MHTML (.mhtml).
    $RenderOutput = $RS.Render($format,
        $deviceInfo,
        [ref] $extension,
        [ref] $mimeType,
        [ref] $encoding,
        [ref] $warnings,
        [ref] $streamIDs
    )

    #   Determine file name
    $parts = $report.ReportPath.Split("/")
    $filename = $parts[-1] + "."
    switch($format)
    {
        "EXCEL" { $filename = $filename + "xls" } 
        "WORD" { $filename = $filename + "doc" }
        "IMAGE" { $filename = $filename + "tif" }
        default { $filename = $filename + $format }
    }

    if($outputpath.EndsWith("\\"))
    {
        $filename = $outputpath + $filename
    } else
    {
        $filename = $outputpath + "\" + $filename
    }

    $filename

    # Convert array bytes to file and write
    $Stream = New-Object System.IO.FileStream($filename), Create, Write
    $Stream.Write($RenderOutput, 0, $RenderOutput.Length)
    $Stream.Close()
}

$RS = GetRSConnection -server "DEVBOX" -instance "ReportServer_DEV"
$report = GetReport -RS $RS -reportPath "folder name/report name"

$params = @()
$params = AddParameter -params $params -name "Month" -val "201311"

GetReportInformat -RS $RS -report $report -params $params -outputpath "i:\test" -format "EXCEL"