powershellreporting-servicesssrs-2017

Create Excel file from SSRS report with PowerShell


I want to run a .ps1 script to automatically generate Excel files from an already published report in SSRS 2017.

Here is my script :

$reportServerURI = "http://NOCSQL05:80/ReportServer/"
$reportPath = "/Folder/Project"
$format = "EXCELOPENXML"
$parameters = @()

# Update these variables with the correct credentials
$username = "ValidUsr"
$password = "ValidPwd"

$RS = New-WebServiceProxy -Class 'ReportingService2010' -NameSpace 'Microsoft.SqlServer.ReportingServices2017' -Uri $reportServerURI

if($RS -ne $null) {
    $RS.Credentials = New-Object System.Net.NetworkCredential($username, $password)
    $Report = $RS.LoadReport($reportPath, $null)
    if($Report -ne $null) {
        $RS.SetExecutionParameters($parameters, "nl-nl") > $null
        $RenderOutput = $RS.Render($format, $null, [ref] $null, [ref] $null, [ref] $null, [ref] $null, [ref] $null)
        if($RenderOutput -ne $null) {
            $Stream = New-Object System.IO.FileStream("C:\Users\TM0658\Documents\report.xlsx", [System.IO.FileMode]::Create)
            $Stream.Write($RenderOutput, 0, $RenderOutput.Length)
            $Stream.Close()
        }
    }
}

I made sure from the Server Configuration manager that the server url was valid, and that the user has the permissions.

However I still get this :

New-WebServiceProxy : The request failed with HTTP status 401: Unauthorized.
At C:\Users\TM0658\Documents\SSRS_Export.ps1:11 char:7
+ $RS = New-WebServiceProxy -Class 'ReportingService2010' -NameSpace 'M ...
+       ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : ObjectNotFound: (http://nocsql05/ReportServer/:Uri) [New-WebServiceProxy], WebException
    + FullyQualifiedErrorId : WebException,Microsoft.PowerShell.Commands.NewWebServiceProxy

I did not found solutions on the web and all similar scripts are from 15 years ago,
So did anyone achieved to create Excel files from published reports in SSRS 2017 ? If you have another solution than Powershell maybe it can do the trick


Solution

  • I resolved this by deleting this line

    $RS.Credentials = New-Object System.Net.NetworkCredential($username, $password)
    

    and modifying the "New-WebServiceProxy"

    $RS = New-WebServiceProxy -Uri $reportServerURI -Class 'ReportingService2010' -NameSpace 'Microsoft.SqlServer.ReportingServices2017' -UseDefaultCredential