How to migrate SSRS reports to Power BI Report Server?

[lorcz]

I found this Microsoft documentation but I couldn't anywhere on the page what "-e" means. I looked at the ssrs_migration.rss file and there is no mention of what -e Mgtm2010 even means. Do I need to use -e in my environment? If so, instead of Mgmt2010, what should it be? Thanks!

[lorcz]

Any suggestions? From within that server or outside, I'm able to access the remote server with my credentials.

[sevenhills]

If you are looking for documentation of parameters for rs.exe, you can check this URL: https://docs.microsoft.com/en-us/sql/reporting-services/tools/rs-exe-utility-ssrs?view=sql-server-ve... 

If you want to know more about "-e", that exists for backward compatibility and Yes you have to use as "-e Mgmt2010"

Other links of interest in migrating reports

• https://businesswintelligence.com/content/80/ssrs-simple-migration-script
• https://www.c-sharpcorner.com/article/migrate-existing-native-ssrs-reports-to-power-bi-report-server...
•  

[lorcz]

Thank you for the response. This is the error I'm getting now.

Could not connect to server: https://sourceServer.myComp.org/ReportServer/ReportService2010.asmx

This is the command I ran.

RS.exe -i C:\Scripts\ssrs_migration.rss -e Mgmt2010 -s https://sourcesServer.myComp.org/ReportServer/ -u DOMAIN\userName -p "password" -v ts="http://destinationServer.myComp.org/ReportServer/" -v tu="DOMAIN\userName" -v tp="password"

[sevenhills]

Can you try browsing in a chrome or edge browser directly?

https://sourceServer.myComp.org/ReportServer/ReportService2010.asmx 

Note: if you have an instance, you may need to use it.

Say, https://sourceServer.myComp.org/ReportServer_SSRSDev/ReportService2010.asmx 

You will be seeing 

<wsdl:definitions xmlns:s="http://www.w3.org/2001/XMLSchema" xmlns:soap12="http://schemas.xmlsoap.org/wsdl/soap12/" xmlns:http="http://schemas.xmlsoap.org/wsdl/http/" xmlns:mime="http://schemas.xmlsoap.org/wsdl/mime/" xmlns:tns="http://schemas.microsoft.com/sqlserver/reporting/2010/03/01/ReportServer" xmlns:soap="http://schemas.xmlsoap.org/wsdl/soap/" xmlns:tm="http://microsoft.com/wsdl/mime/textMatching/" xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/" targetNamespace="http://schemas.microsoft.com/sqlserver/reporting/2010/03/01/ReportServer" xmlns:wsdl="http://schemas.xmlsoap.org/wsdl/">
<wsdl:documentation xmlns:wsdl="http://schemas.xmlsoap.org/wsdl/">The Reporting Services Web Service enables you to manage a report server and its contents including server settings, security, reports, subscriptions, and data sources.</wsdl:documentation>
<wsdl:types>
<s:schema elementFormDefault="qualified" targetNamespace="http://schemas.microsoft.com/sqlserver/reporting/2010/03/01/ReportServer">
<s:element name="CreateCatalogItem">
<s:complexType>

[lorcz]

Yes, I can access the source report server through the browser.

[sevenhills]

Now we are getting into troubleshooting mode. Can be iterative till we know the issue for connecting. Since we dont have any info, I will ask you to do these.

1. with "-t" to get more details, -t is for tracing i.e., run the same command above with -t

"rs.exe ... ... ... -e Mgmt2010 -t"

See if the info provided, gives any info. 

2. Let us do simple connect to retrieve server info, and also test the connection

2.1. Create a file "rs_GetReportServerConfigInfo.rss", you can call the file name of your like

Public Sub Main()
	DIM results as String

	TRY
		rs.Credentials = System.Net.CredentialCache.DefaultCredentials
    		results = rs.GetReportServerConfigInfo("FALSE")
		Console.WriteLine(results)
	Catch e As IOException
		Console.WriteLine(e.Message)
	End Try
End Sub

2.2. Command prompt (run as administrator)

(note: I used http, you can use https also if your SSL site is working fine)

 rs.exe -i C:\Users\...\rs_GetReportServerConfigInfo.rss -s "http://.../Reportserver" -e Mgmt2010 -t

Substitute the .rss file location and URL for the reportserver and run the command. If it works well then you should be seeing info in the xml as below:

<ServerConfigInfo><Server><MachineName>...
The command completed successfully

If any errors, it will display here...

Optional, but worth checking the logs (both eventviewer and report logs)

Reference: 

https://docs.microsoft.com/en-us/dotnet/api/reportservice2010.reportingservice2010.getreportserverco...

https://docs.microsoft.com/en-us/sql/reporting-services/report-server/reporting-services-log-files-a...

[lorcz]

For troubleshooting 1:

RS.exe -i C:\Scripts\ssrs_migration.rss -e Mgmt2010 -t https://sourcesServer.myComp.org/ReportServer/ -u DOMAIN\userName -p "password" -v ts="http://destinationServer.myComp.org/ReportServer/" -v tu="DOMAIN\userName" -v tp="password"

This is the error that came back.

Unrecognized argument https://sourcesServer.myComp.org/ReportServer/

[sevenhills]

Looks like you have a typo in your syntax -t does not have any argument, -s has the argument ... use the original command you had and Add -t to the end and try again

Did you try the trouble shoot 2?

[lorcz]

I put the -s back and I got the same error as when I performed troubleshoot step 2.

Here's the code I ran.

RS.exe -i C:\Scripts\rs_GetReportServerConfigInfo.rss -e Mgmt2010 -t -s https://sourcesServer.myComp.org/ReportServer/ -u DOMAIN\userName -p "password" -v ts="http://destinationServer.myComp.org/ReportServer/" -v tu="DOMAIN\userName" -v tp="password"

And here's the error:

System.Exception: Could not connect to server: https://sourcesServer.myComp.org/ReportServer/ReportService2010.asmx ---> System.Net.WebException: Unable to connect to the remote server ---> System.Net.Sockets.SocketException: An attempt was made to access a socket in a way forbidden by its access permissions 192.168.1.148:443
at System.Net.Sockets.Socket.DoConnect(EndPoint endPointSnapshot, SocketAddress socketAddress)
at System.Net.ServicePoint.ConnectSocketInternal(Boolean connectFailure, Socket s4, Socket s6, Socket& socket, IPAddress& address, ConnectSocketState state, IAsyncResult asyncResult, Exception& exception)
--- End of inner exception stack trace ---
at System.Net.HttpWebRequest.GetRequestStream(TransportContext& context)
at System.Net.HttpWebRequest.GetRequestStream()
at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)
at Microsoft.SqlServer.ReportingServices2010.ReportingService2010.IsSSLRequired()
at Microsoft.ReportingServices.ScriptHost.Management2010Endpoint.PingService(String url, String userName, String password, String domain, Int32 timeout)
at Microsoft.ReportingServices.ScriptHost.ScriptHost.DetermineServerUrlSecurity()
--- End of inner exception stack trace ---
at Microsoft.ReportingServices.ScriptHost.ScriptHost.DetermineServerUrlSecurity()
at Microsoft.ReportingServices.ScriptHost.ScriptHost.InstanceMain()
at Microsoft.ReportingServices.BaseCmdLine.CommandLineMain(String[] args, BaseCmdLine instance)

[sevenhills]

Let us check whether this is port issue or configuration issue.  

1. Telnet for port 443, it should not give any error, but worth checking, as you are able to browse the url

An article, that talks about checking a port on a remote server ... 

https://kb.synology.com/en-me/DSM/tutorial/Whether_TCP_port_is_open_or_closed#:~:text=your%20Linux%2....

2. Firewall access for port 443, any block. it should not but worth checking, as you are able to browse the url

3. Connect via Powershell and get reports list

$ReportServerUri = 'http://<your report server>/Reports/ReportService2010.asmx'
$SourceFolderPath = '/' # Replace with the folder path you have access. without "/" at the end, Say /BI_Finance
$items = $null
$Proxy = $null

$Proxy = New-WebServiceProxy -Uri $ReportServerUri -Namespace SSRS.ReportingService2010 -UseDefaultCredential
$items = $Proxy.ListChildren($sourceFolderPath, $true) | Select-Object Type, Path, Name

if ($items -eq $null) { 
    Write-Host "No Items found!" 
}
else {
    $items
}

# ------------------------------------
Write-Host "----------------------------------"
Write-Host "ReportServerUri: " $ReportServerUri
Write-Host "SourceFolderPath: " $SourceFolderPath
Write-Host "----------------------------------"
Write-Host "Proxy: " $Proxy
Write-Host "----------------------------------"
Write-Host "Items:" 
$items
Write-Host "----------------------------------"

4. Now replace with https Report Server Uri in the above powershell code.

it will not work, indicates that Report Server need to be configured for web service access.

5. Fix the Report Server Configuration Manager

On the report server, after connecting to Report Server Configuration Manager, click "Web Service URL" and see whether https port is configured or any urls already configured.

Most likely http is only configured. You need to add https.

[lorcz]

1. telnet to port 443 works fine

2. there is no firewall blocking port 443

3. I got the following error.

[sevenhills]

Did the powershell script work with http (and I see your image has https)? 

If yes, then I posted the fix above

[lorcz]

I got similar error.

[sevenhills]

Please do

On the report server, Open, Report Server Configuration Manager,  after connecting to Report Server Configuration Manager, click "Web Service URL"

Check if you see any details on the URLs for web service ... 

What is expected? atleast one server with the same name you used above. May be http, or https.

If it is not configured properly in the first time, you may see http://localhost/ or 127.0.0.1

[lorcz]

This is the source SSRS.

And this the destination or target Power BI Report Server.

I'm running the powershell script from within the target PBRS.

[sevenhills]

Using the source server url, Please run the Powershell script on the source server and see it works. It should work.

It may sound repeatitive task, but want to check the source server, powershell works fine. 

$ReportServerUri = 'http://<your report server>/Reports/ReportService2010.asmx'
$SourceFolderPath = '/' # Replace with the folder path you have access. without "/" at the end, Say /BI_Finance
$items = $null
$Proxy = $null

$Proxy = New-WebServiceProxy -Uri $ReportServerUri -Namespace SSRS.ReportingService2010 -UseDefaultCredential
$items = $Proxy.ListChildren($sourceFolderPath, $true) | Select-Object Type, Path, Name

if ($items -eq $null) { 
    Write-Host "No Items found!" 
}
else {
    $items
}

# ------------------------------------
Write-Host "----------------------------------"
Write-Host "ReportServerUri: " $ReportServerUri
Write-Host "SourceFolderPath: " $SourceFolderPath
Write-Host "----------------------------------"
Write-Host "Proxy: " $Proxy
Write-Host "----------------------------------"
Write-Host "Items:" 
$items
Write-Host "----------------------------------"

When you repeat the same from target server, and using the source server URL, it will fail, based on your previous posts. If this is true, please let me know. This indicates that target server cannot access the source server. 

[lorcz]

Okay, I tested the script again.

Public Sub Main()
	DIM results as String

	TRY
		rs.Credentials = System.Net.CredentialCache.DefaultCredentials
    		results = rs.GetReportServerConfigInfo("FALSE")
		Console.WriteLine(results)
	Catch e As IOException
		Console.WriteLine(e.Message)
	End Try
End Sub

And the result is successful.

However, when I ran the actual migration script, I got this.

I don't think it the issue is low disk since it still shows 15GB free space on the hard drive and the reports are not that much. As for the connection failure, I'm not sure if it's referring to failing to connect to source server or target. Also, do I need to change the folder name on either source or target? It's now set to /ReportServer/ instead of /Budget/.

[sevenhills]

A) I cannot read the image ... not sure what is the error

B) Can you check the Event viewer logs on both servers and SSRS logs (%ProgramFiles%\Microsoft SQL Server\MSSQL.x\Reporting Services\LogFiles) and see anything 

C) Let us do one more thing, try to get the reports to your local computer and see the source server is good ...

Out-RsFolderContent -Proxy $proxy -RsFolder "/Budget" -Destination 'C:\SSRS_Out' -Recurse

If you see the reports, datasources as files ... that means the source server is exporting properly.

[lorcz]

A: I think if you clicked on the image, it'll zoom in for you. But here's the error.

Retrieve and report the list of items that will be migrated. You can cancel the script after step 1 if you do not want to start the actual migration.

Retrieving schedules:
Unhandled exception:
An error occurred within the report server database. This may be due to a connection failure, timeout or low disk condition within the database.

B: I don't think this log message is related to the issue but here's the log.

On the source: "C:\Program Files\Microsoft SQL Server Reporting Services\SSRS\LogFiles\RSHostingService_2022_05_20_00_00_00.log"

2022-05-20 11:24:29.0347|INFO|51|Deleting expired log file: C:\Program Files\Microsoft SQL Server Reporting Services\SSRS\LogFiles\RSPortal_2022_04_29_04_55_26.log Last written: 4/29/2022 4:55:26 AM
2022-05-20 11:24:29.0347|WARN|51|Exception deleting expired log fileSystem.IO.IOException: The process cannot access the file 'C:\Program Files\Microsoft SQL Server Reporting Services\SSRS\LogFiles\RSPortal_2022_04_29_04_55_26.log' because it is being used by another process.
at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
at System.IO.File.InternalDelete(String path, Boolean checkHost)
at Microsoft.BIServer.HostingEnvironment.Logger.DeleteExpiredFilesInternal(Int32 keepUntilDays, FileInfo[] logFiles)
2022-05-20 11:24:39.0569|INFO|22|Deleting expired log file: C:\Program Files\Microsoft SQL Server Reporting Services\SSRS\LogFiles\RSPortal_2022_04_29_04_55_26.log Last written: 4/29/2022 4:55:26 AM
2022-05-20 11:24:39.0569|WARN|22|Exception deleting expired log fileSystem.IO.IOException: The process cannot access the file

On the target: "C:\Program Files\Microsoft Power BI Report Server\PBIRS\LogFiles\RSHostingService_2022_05_20_00_00_03.log"

2022-05-20 11:24:26.1215|INFO|58|Catalog max connection pool size: 100
2022-05-20 11:24:26.1215|INFO|58|Deleting expired log file: C:\Program Files\Microsoft Power BI Report Server\PBIRS\LogFiles\msmdsrv.log Last written: 5/3/2022 1:09:34 PM
2022-05-20 11:24:26.1371|WARN|58|Exception deleting expired log fileSystem.IO.IOException: The process cannot access the file 'C:\Program Files\Microsoft Power BI Report Server\PBIRS\LogFiles\msmdsrv.log' because it is being used by another process.
at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
at System.IO.File.InternalDelete(String path, Boolean checkHost)
at Microsoft.BIServer.HostingEnvironment.Logger.DeleteExpiredFilesInternal(Int32 keepUntilDays, FileInfo[] logFiles)
2022-05-20 11:24:36.1625|INFO|58|Catalog max connection pool size: 100
2022-05-20 11:24:36.1782|INFO|58|Deleting expired log file: C:\Program Files\Microsoft Power BI Report Server\PBIRS\LogFiles\msmdsrv.log Last written: 5/3/2022 1:09:34 PM
2022-05-20 11:24:36.1782|WARN|58|Exception deleting expired log fileSystem.IO.IOException: The process cannot access the file

C: Do I simply run this code in PowerShell on the source server?

Out-RsFolderContent -Proxy $proxy -RsFolder "/Budget" -Destination 'C:\SSRS_Out' -Recurse

[sevenhills]

Related to (A) and (B) it needs some attention, looks like something is going on. you need to check the logs (Event viewer, PBIRS logs)

Related to (C), you need to use the before powershell code and this line ... executing alone that line wont work.

$ReportServerUri = 'http://db22/ReportServer/ReportService2010.asmx'
$SourceFolderPath = '/Budget'
$Proxy = $null

$Proxy = New-WebServiceProxy -Uri $ReportServerUri -Namespace SSRS.ReportingService2010 -UseDefaultCredential
Out-RsFolderContent -Proxy $proxy -RsFolder "/Budget" -Destination 'C:\SSRS_Out' -Recurse

Idea is to get all the report server content to a local drive and test it works or not.

If this works, atleast you can take a backup or get a copy of all reports, datasets, datasources and more from the source.