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