none
Integrate SSRS with SharePoint 2010

    Question

  • Hello,

    We have an existing 2008 R2 SSRS server, and the database is configured as native. Recently, we also set up a SharePoint 2010 site, and would like to integrate reports from the SSRS server in the SharePoint site.

    From what I've read, the SSRS database needs to be in SharePoint integrated mode. Since the report server has already been set up, is there any way to do this? Can the existing database be altered? Are we out of luck?

    If I change the existing ReportServer database to SharePoint integrated mode, will it have any effect on existing reports/functionality?

    Thanks for any assistance.

    Tomt

    Saturday, March 10, 2012 1:14 AM

Answers

  • THere is no separate Report Manager in integrated mode. SharePoint becomes the Report Manager, and therefore, they're only available via SharePoint (and of course through the web services and web services interface)

    John

    • Marked as answer by TomT49 Tuesday, May 22, 2012 5:47 PM
    Friday, May 11, 2012 3:16 AM
  • For the "Instance Name" field, instead of entering just MSSQLSERVER, could you try entering either <SERVER NAME>\MSSQLSERVER or just <SERVER NAME>?  The label for that field is misleading since the server name is also needed (something to fix in the next version :-)).

    Thursday, April 26, 2012 9:29 PM
    Moderator

All replies

  • As I recall you cannot switch the database easily between SharePoint Integrated Mode and Native Mode as the the Database Structure is completely different. To be able to complete this you will need to download all your reports available on the Native Mode to your Desktop, switch your SSRS to integrated Mode (complete configuration) and then using the BIDS u will need to deploy your old reports on the new version of SSRS.

    Teddy Bejjani - BI Specialist @ Netways

    • Proposed as answer by ShashiV Thursday, April 26, 2012 5:16 PM
    • Unproposed as answer by ShashiV Thursday, April 26, 2012 5:17 PM
    Sunday, March 11, 2012 2:11 PM
  • Hi TomT49,

    We need to create a Report Server database for SharePoint Integrated mode by using the Reporting Services Configuration Manager. If we configure a Report Server run in SharePoint Integrated mode successfully, we can directly deploy reports to SharePoint sites.

    For more detail information, please refer to the articles below:
    Configuring Reporting Services for SharePoint 2010 Integration: http://msdn.microsoft.com/en-us/library/bb326356.aspx
    How to: Create a Report Server Database for SharePoint Integrated Mode (Reporting Services Configuration): http://msdn.microsoft.com/en-us/library/bb283151.aspx
    Configuring Reporting Services 2008 R2 in SharePoint 2010 Integrated Mode: http://dinesql.blogspot.com/2010/06/configuring-reporting-services-2008-r2.html

    Regards,
    Bin Long

    TechNet Subscriber Support
    If you are
    TechNet Subscription user and have any feedback on our support quality, please send your feedback here.


    Bin Long

    TechNet Community Support

    Monday, March 12, 2012 3:12 AM
    Moderator
  • As you have seen from the other replies, you need to migrate data from native to SharePoint mode report server. We are creating a tool to help you with just that. We are really close to releasing it. Check back after a week or so :).
    Monday, March 12, 2012 4:33 AM
    Moderator
  • Thanks James, I assume from your reply you will post here when this tool is available?
    Monday, March 12, 2012 4:42 PM
  • Thanks for the reply, unfortunately we already have our report server set up with existing reports, and need to use that system. It appears a tool is coming to migrate our existing data.

    Tom

    Monday, March 12, 2012 4:43 PM
  • Thanks Teddy, I'm surprised there isn't a built in way to do this, as I'm sure this situation cannot be uncommon.
    Monday, March 12, 2012 4:44 PM
  • Yes, I will post a link to the tool. We are currently testing the tool against a production system.
    Monday, March 12, 2012 6:18 PM
    Moderator
  • We are finalizing the first version of this tool. Stay tuned.
    Monday, March 26, 2012 8:33 PM
    Moderator
  • The tool is live on Microsoft download now. Feedback is welcome :)

    Reporting Services Migration Tool

    Friday, April 20, 2012 9:51 PM
    Moderator
  • Hi James

    The tool works through the web service URL, but it stubbornly doesn't want to work through the WMI provider - this leads to a loss of some functions. I've tested it on both 2008R2 and 2012 Native mode servers - same result. Where can I provide this feedback?

    Cheers


    John

    Thursday, April 26, 2012 2:29 PM
  • Hi John, please post your feedback on this forum. What errors are you getting?
    
    Thursday, April 26, 2012 4:30 PM
    Moderator
  • Thanks

    Here are the errors:

    RSMigrationUI.exe Information: 0 : Caught WMIProviderException while connecting to v11 WMI namespace.  Falling back to v10 WMI namespace...
    RSMigrationUI.exe Information: 0 : Caught WMIProviderException while connecting to v10 WMI namespace.  Falling back to v9 WMI namespace...
    RSMigrationUI.exe Information: 0 : Caught WMIProviderException while connecting to v9 WMI namespace.
    RSMigrationUI.exe Error: 0 : System.Exception: Cannot find  WMI instance for specified RS instance
       at Microsoft.ReportingServices.Migration.Common.WmiUtil.GetWmiProvider(String serverName, String instanceName, GetWmiProviderDelegate getWmiProvider)
       at Microsoft.ReportingServices.Migration.Engine.Configuration.CalculateDerivedSettings(String nativeRsInstance)
       at Microsoft.ReportingServices.Migration.Engine.Configuration.Initialize(String migrationFolder, String password, String nativeRsUrl, String nativeRsInstance, Boolean includeReportHistory)
       at RSMigrationUI.MainWindow.btnGenerateMigrationScript_Click(Object sender, RoutedEventArgs e)

    In both cases they are default instances, and the value entered for Instance Name is MSSQLSERVER


    John

    Thursday, April 26, 2012 7:52 PM
  • 
    

    - Are you running MigrationTool on the machine where the native instances are running? (You don't have to but I'd like to know if that was the case.)

    - Migration Tool connects to WMI the same way SSRS Configuration Tool does. Is it possible for you to try running SSRS configuration Tool on the same box where MigrationTool is run? If so do you get any errors?

    Thursday, April 26, 2012 8:28 PM
    Moderator
  • Thanks James. I am running on the same machine, and I can run Configuration Manager just fine - this is true of both server that I've tried (One being 2012, and the other 2008R2)

    John

    Thursday, April 26, 2012 9:20 PM
  • For the "Instance Name" field, instead of entering just MSSQLSERVER, could you try entering either <SERVER NAME>\MSSQLSERVER or just <SERVER NAME>?  The label for that field is misleading since the server name is also needed (something to fix in the next version :-)).

    Thursday, April 26, 2012 9:29 PM
    Moderator
  • Tristan - Brilliant. As you say - it's not Instance name - it's server\Instance name.

    Thanks!


    John

    Friday, April 27, 2012 11:47 AM
  • I think this thread is really helpful for all those who wants to move from SSRS Native mode to Sharepoint integrated mode and especially when u have lots of reports on the server.

    Ok here is my story, I ran the tool and it ran succesfully, it created folder structure and lot of files, but I didn't generate the final Migration.ps1 file instead I see MigrationUtilities.psm1 file. I checked the log for any errors, I found the below error line for few reports (but I assume this error is only confined to a particular report). In my case I have 700 reports on the server and I wont mind if I am not able to move few reports as I can always do it manually.

    Any help on this will be much appreciated.

    RSMigrationUI.exe Error: 0 : An error occurred while generating migration script for operation 'Migrate subscriptions for Report '/XXX'': System.NullReferenceException: Object reference not set to an instance of an object.
       at Microsoft.ReportingServices.Migration.Engine.Tasks.BaseCreateSubscriptionsTask`2.GeneratePowerShellScriptInternal()
       at Microsoft.ReportingServices.Migration.Engine.TaskList.GeneratePowerShellScript()
    RSMigrationUI.exe Information: 0 : Creating migration script for operation 'Migrate subscriptions for Report '/XXX''

    Thanks

    -Riaz

    Thursday, May 3, 2012 1:31 PM
  • Hi Riaz, thanks for reporting this issue. Sorry about the error. This is probably a bug and we want to improve the tool. The tool is to design to log and ignore errors. The error should not stop the tool from generating Migration.ps1.

    1. Can you tell we that are the last 10 lines of traces in the log file?

    2. Can you tell me the details of the subscription that is causing the NullReferenceException? Such as: timed or data driven; delivery settings. My guess is one of the properties were not set and is causing the problem.

    Thursday, May 3, 2012 5:58 PM
    Moderator
  • Thanks! Great blog. It's the first one I've seen on the tool :)
    Thursday, May 3, 2012 7:13 PM
    Moderator
  • I was wondering what effects migrating to SharePoint integrated mode will have on existing functionality in a report server which has been up and running in native mode. E.g., will all existing reports be preserved (will the process be transparent to users relying on existing reports)? Are there any other implications of running the tool, e.g. re-deploying or configuration of any kind?

    I want to make sure we don't lose any existing functionality, etc.

    Thanks

    Monday, May 7, 2012 7:01 PM
  • You should be able to run the tool while the native mode server is in operation. Because the tool doesn't run queries/SOAP requests in a transaction, you may get inconsistent backup or the native server. For example, if the report data source was updated while the tool is running, the tool might backup old or new data source depending on timing. The tool is "read" only on the native server. It doesn't modify any state on the native server. You will not lose any existing functionality on it.
    Monday, May 7, 2012 7:20 PM
    Moderator
  • Hi James, thanks for the quick response on the thread.

    1. Here are the last 10 lines of the log (seems like a good exit log).

    RSMigrationUI.exe Information: 0 : Creating migration script for operation 'Migrate properties for Report '/X/Yreport''
    RSMigrationUI.exe Information: 0 : Creating migration script for operation 'Migrate properties for Report '/XX/YY''
    RSMigrationUI.exe Information: 0 : Creating migration script for operation 'Migrate properties for Report '/ZZ/AA''
    RSMigrationUI.exe Information: 0 : Creating migration script for operation 'Migrate properties for Folder '/B/4.0''
    RSMigrationUI.exe Information: 0 : Creating migration script for operation 'Migrate properties for Folder '/C/4.0''
    RSMigrationUI.exe Information: 0 : Creating migration script for operation 'Migrate properties for Report '/C/D_Report''
    RSMigrationUI.exe Information: 0 : Creating migration script for operation 'Migration script cleanup'
    RSMigrationUI.exe Information: 0 : Migration script generation ended at 05/03/2012 15:06:02
    RSMigrationUI.exe Information: 0 : Total elapsed time: 00:06:22.5625000
    RSMigrationUI.exe Information: 0 : Migration files was successfully generated.

    2. The subscription was email based and was selected to run only once - I dont think this should be a problem.

    I am thinking of running the tool again, by deleting these subscriptions (its a backup copy of the prod server) and see if it creates the Migration.ps1, I will tell you how that goes.

    Hoping to see this tool fix my migration worries.

    Thanks again for the tool and all the support, its great.

    -Riaz

    Wednesday, May 9, 2012 7:08 AM
  • Hi Riaz,

    Based on the logs, it seems like Migration.ps1 should have been successfully created in the migration file folder specified when you ran the tool.  It would not be in the Scripts folder.  Right now, the only file in that folder should be MigrationUtilities.psm1, as you saw.  If Migration.ps1 does not exist, try running the tool again and let us know how it goes.

    Thanks.

    Wednesday, May 9, 2012 6:29 PM
    Moderator
  • Thanks James. One other question: if, for any reason things don't go well (e.g. problem with existing reports, etc.), can the report server be returned to exactly how it was by simply restoring a backup taken prior to the conversion process?

    Thanks

    Wednesday, May 9, 2012 7:06 PM
  • The tool does not modify the source/native server. As for the target/SharePoint server, the tool doesn't support roll back (probably a feature good to add). To restore the target server you need to do the usual backup/restore action (i.e. restore SharePoint content DB, restore SSRS catalog and temp DB, etc.)

    
    Wednesday, May 9, 2012 7:12 PM
    Moderator
  • Thanks James. I guess I'm a bit confused. I was under the impression the tool modified the SSRS sever, however it appears you are saying the SharePoint server is modified instead? Sorry if I'm missing something here, I just want to be clear on what exactly happens when the tool is run.
    Wednesday, May 9, 2012 8:30 PM
  • There are two SSRS servers in question here:

    - The native mode SSRS server which is the source server

    - The SharePoint mode server which is the target server: this includes the SSRS server integrated with SharePoint, plus SharePoint server itself.

    The way to think about this is that the tool "reads" from the source/native server, and the powershell script it generates "writes" to the target/sharepoint server (including SSRS server in SharePoint integrated mode, and the SharePoint server). It doesn't modify the source/native server in any way.

    Wednesday, May 9, 2012 8:37 PM
    Moderator
  • Thanks. But it does change the SSRS (source/native) to SharePoint Integrated mode?
    Wednesday, May 9, 2012 8:41 PM
  • It does not. The tools migrates contents only. It does not change any configuration of either source or target servers.
    Wednesday, May 9, 2012 8:43 PM
    Moderator
  • I see. The reason for my confusion comes from my orginal post, at the start of this thread. My understanding was to get SharePoint to be able to use SSRS reports, the SSRS server needed to be configured for SharePoint integrated mode. Since our server was set up before we started using SharePoint, it was configured in Native mode.

    I was asking, orginally, if there was a way to change the SSRS server's configuration from Native to SharePoint integrated mode. I thought the tool was the solution for that.

    Wednesday, May 9, 2012 8:52 PM
  • I see. The tool can help you migrate the the contents you developed for native mode to another SharePoint mode SSRS server, but it does not change the mode of the native SSRS server. You could discard the catalog databases of your native SSRS server, and create an sharepoint SSRS server with empty catalog database (using the SSRS configuration tool). Then you can restore the backup of your native mode server.
    Wednesday, May 9, 2012 8:55 PM
    Moderator
  • Ok, thanks. Based on my original question, I misunderstood the purpose of the tool. So it appears there is still no way to modify an SSRS server to change it from native to integrated....
    Wednesday, May 9, 2012 9:01 PM
  • Tom - it's not the server that needs to be changed, but the RS database. However there's no way to convert from one to the other (except via this tool).

    Using the tool you could:

    1. Back up the Report Server

    2. Run SSRS config and create a new RS database in Integrated mode

    3, Create the requisite libraries in SharePoint to store content

    4. Restore the files created in step 1 to the same (now SharePoint integrated mode) server.


    John

    Thursday, May 10, 2012 12:41 AM
  • John, thanks very much for the clarifications. All we're looking to do at this point is have the ability to use

    SQL Server Reporting Services Report Viewer web parts in existing SharePoint pages. I'm still a bit unclear as to this relates to your step 3 (creating requisite libraries). Would this be necessary to just use the web part?

    Thanks for your help and patience, I'm a bit new to SharePoint.

    Tom

    Thursday, May 10, 2012 4:40 PM
  • No problem.

    WHen you use Integrated Mode Reporting Services, the Reporting Services atrifacts (Reports, Connections, models) are no longer stored in the Reporting Services databases, but in SharePoint Libraries. THe libraries in question need to have been content type enabled, and using the relevant RS Content Types. THis step needs to be performed because once in SP Integrated mode, RS needs a place to put all of the things that it had removed from the old Native mode instance.


    John

    Thursday, May 10, 2012 6:05 PM
  • Thanks John. When the SSRS is in integrated mode, what happens to the existing RS reports, etc.? Are they still available in RS? In other words, will users still be able to go to SSRS to pull up the reports, or will they only be available via SharePoint?

    Tom

    Thursday, May 10, 2012 6:29 PM
  • THere is no separate Report Manager in integrated mode. SharePoint becomes the Report Manager, and therefore, they're only available via SharePoint (and of course through the web services and web services interface)

    John

    • Marked as answer by TomT49 Tuesday, May 22, 2012 5:47 PM
    Friday, May 11, 2012 3:16 AM
  • Hi Tristan,

    I ran the Migration.ps1 and I got all errors, only pasting few lines here. Really appreciate your quick response.

    05/22/2012 10:00:09 Information: Migration started at 05/22/2012 10:00:09
    05/22/2012 10:00:09 Error: Error retrieving shared schedules from : System.Management.Automation.RuntimeException: You cannot call a method on a null-valued expression.
       at System.Management.Automation.ParserOps.CallMethod(Token token, Object target, String methodName, Object[] paramArray, Boolean callStatic, Object valueToSet)
       at System.Management.Automation.MethodCallNode.InvokeMethod(Object target, Object[] arguments, Object value)
       at System.Management.Automation.MethodCallNode.Execute(Array input, Pipe outputPipe, ExecutionContext context)
       at System.Management.Automation.ParseTreeNode.Execute(Array input, Pipe outputPipe, ArrayList& resultList, ExecutionContext context)
       at System.Management.Automation.StatementListNode.ExecuteStatement(ParseTreeNode statement, Array input, Pipe outputPipe, ArrayList& resultList, ExecutionContext context).ToString()
    05/22/2012 10:00:09 Error: Error retrieving shared schedules from : System.Management.Automation.RuntimeException: You cannot call a method on a null-valued expression.
       at System.Management.Automation.ParserOps.CallMethod(Token token, Object target, String methodName, Object[] paramArray, Boolean callStatic, Object valueToSet)
       at System.Management.Automation.MethodCallNode.InvokeMethod(Object target, Object[] arguments, Object value)
       at System.Management.Automation.MethodCallNode.Execute(Array input, Pipe outputPipe, ExecutionContext context)
       at System.Management.Automation.ParseTreeNode.Execute(Array input, Pipe outputPipe, ArrayList& resultList, ExecutionContext context)
       at System.Management.Automation.StatementListNode.ExecuteStatement(ParseTreeNode statement, Array input, Pipe outputPipe, ArrayList& resultList, ExecutionContext context).ToString()
    05/22/2012 10:00:09 Error: Error retrieving shared schedules from : System.Management.Automation.RuntimeException: You cannot call a method on a null-valued expression.
       at System.Management.Automation.ParserOps.CallMethod(Token token, Object target, String methodName, Object[] paramArray, Boolean callStatic, Object valueToSet)
       at System.Management.Automation.MethodCallNode.InvokeMethod(Object target, Object[] arguments, Object value)
       at System.Management.Automation.MethodCallNode.Execute(Array input, Pipe outputPipe, ExecutionContext context)
       at System.Management.Automation.ParseTreeNode.Execute(Array input, Pipe outputPipe, ArrayList& resultList, ExecutionContext context)
       at System.Management.Automation.StatementListNode.ExecuteStatement(ParseTreeNode statement, Array input, Pipe outputPipe, ArrayList& resultList, ExecutionContext context).ToString()
    05/22/2012 10:00:09 Error: Error retrieving shared schedules from : System.Management.Automation.RuntimeException: You cannot call a method on a null-valued expression.
       at System.Management.Automation.ParserOps.CallMethod(Token token, Object target, String methodName, Object[] paramArray, Boolean callStatic, Object valueToSet)
       at System.Management.Automation.MethodCallNode.InvokeMethod(Object target, Object[] arguments, Object value)
       at System.Management.Automation.MethodCallNode.Execute(Array input, Pipe outputPipe, ExecutionContext context)
       at System.Management.Automation.ParseTreeNode.Execute(Array input, Pipe outputPipe, ArrayList& resultList, ExecutionContext context)
       at System.Management.Automation.StatementListNode.ExecuteStatement(ParseTreeNode statement, Array input, Pipe outputPipe, ArrayList& resultList, ExecutionContext context).ToString()
    05/22/2012 10:00:09 Error: Error retrieving shared schedules from : System.Management.Automation.RuntimeException: You cannot call a method on a null-valued expression.
       at System.Management.Automation.ParserOps.CallMethod(Token token, Object target, String methodName, Object[] paramArray, Boolean callStatic, Object valueToSet)
       at System.Management.Automation.MethodCallNode.InvokeMethod(Object target, Object[] arguments, Object value)
       at System.Management.Automation.MethodCallNode.Execute(Array input, Pipe outputPipe, ExecutionContext context)
       at System.Management.Automation.ParseTreeNode.Execute(Array input, Pipe outputPipe, ArrayList& resultList, ExecutionContext context)
       at System.Management.Automation.StatementListNode.ExecuteStatement(ParseTreeNode statement, Array input, Pipe outputPipe, ArrayList& resultList, ExecutionContext context).ToString()
    05/22/2012 10:00:09 Error: Error retrieving shared schedules from : System.Management.Automation.RuntimeException: You cannot call a method on a null-valued expression.
       at System.Management.Automation.ParserOps.CallMethod(Token token, Object target, String methodName, Object[] paramArray, Boolean callStatic, Object valueToSet)
       at System.Management.Automation.MethodCallNode.InvokeMethod(Object target, Object[] arguments, Object value)
       at System.Management.Automation.MethodCallNode.Execute(Array input, Pipe outputPipe, ExecutionContext context)
       at System.Management.Automation.ParseTreeNode.Execute(Array input, Pipe outputPipe, ArrayList& resultList, ExecutionContext context)
       at System.Management.Automation.StatementListNode.ExecuteStatement(ParseTreeNode statement, Array input, Pipe outputPipe, ArrayList& resultList, ExecutionContext context).ToString()

    Regards,

    -Riaz

    Tuesday, May 22, 2012 11:00 AM
  • Could you share the command line you used to run Migration.ps1?  It should look something like:

    .\Migration.ps1 <Target SharePoint site> <Target SharePoint Doclib/Folder> <Target RS Catalog Connection String> <Encryption Password>

    Tuesday, May 22, 2012 5:24 PM
    Moderator
  • Here it is

    PS C:\RSToolScript> 05/22/2012 10:00:09 Information: Migration started at 05/22/2012 10:00:09

    .\Migration.ps1 -TargetSiteUrl http://server/default.aspx -Password pwd123 -TargetFolderUrl "http://server/t1" -TargetConnectionString "Data Source=XXX;Initial Catalog=ReportServer;Integrated Security=True"

    Thanks,

    -Riaz

    Wednesday, May 23, 2012 7:45 AM
  • For -TargetSiteUrl, try specifying http://server, instead of the URL to the home page.  Let us know if that fixes these errors.
    Wednesday, May 23, 2012 5:27 PM
    Moderator
  • I get a different error message now if I remove /default.aspx

    New-WebServiceProxy : Could not find file 'C:\Users\XXX\AppData\Local\Temp\2\5viraqxe.dll'.
    At C:\RSToolScript\Migration.ps1:10 char:30
    + $rs2010 = New-WebServiceProxy <<<<  -uri ($targetSiteUrl + "/_vti_bin/ReportServer/ReportService2010.asmx?wsdl") -nam
    espace RS2010 -class RS2010 -UseDefaultCredential
        + CategoryInfo          : NotSpecified: (:) [New-WebServiceProxy], FileNotFoundException
        + FullyQualifiedErrorId : System.IO.FileNotFoundException,Microsoft.PowerShell.Commands.NewWebServiceProxy

    Migration started at 05/24/2012 12:39:51
    Error retrieving shared schedules from : System.Management.Automation.RuntimeException: You cannot call a method on a nu
    ll-valued expression.
       at System.Management.Automation.ParserOps.CallMethod(Token token, Object target, String methodName, Object[] paramArr
    ay, Boolean callStatic, Object valueToSet)
       at System.Management.Automation.MethodCallNode.InvokeMethod(Object target, Object[] arguments, Object value)
       at System.Management.Automation.MethodCallNode.Execute(Array input, Pipe outputPipe, ExecutionContext context)
       at System.Management.Automation.ParseTreeNode.Execute(Array input, Pipe outputPipe, ArrayList& resultList, ExecutionC
    ontext context)
       at System.Management.Automation.StatementListNode.ExecuteStatement(ParseTreeNode statement, Array input, Pipe outputP
    ipe, ArrayList& resultList, ExecutionContext context).ToString()
    Unable to find type [RS2010.Schedule]: make sure that the assembly containing this type is loaded.
    At C:\RSToolScript\Scripts\MigrationUtilities.psm1:302 char:27
    +     $type = [RS2010.Schedule] <<<<
        + CategoryInfo          : InvalidOperation: (RS2010.Schedule:String) [], RuntimeException
        + FullyQualifiedErrorId : TypeNotFound

    Thursday, May 24, 2012 10:40 AM
  • Have you tried closing and restarting your PowerShell window?  Also, make sure you are running it as administrator.
    Thursday, May 24, 2012 9:41 PM
    Moderator
  • Hi

    You can do this simply call the reports using report viewer  control .


    http://www.c-sharpcorner.com/UploadFile/jayendra/add-report-using-report-viewer-web-parts-in-sharepoint-found/
    Friday, March 8, 2013 4:40 AM