How Get Reports To See Shared Data Sources

Question

I am running 2008 R2

I have an entire set of reports that I have to load into RS. These were developed and sent to our company by a third party. They connect to a shared data source. I uploaded the reports into folders that separated them by function. The problem is that they don't see the data source that the reports are written to use. I created a shared data source and pointed the report to it. The connection tested successfully but when I ran the report I continued to receive the following error:

"The report server cannot process the report or shared dataset. The shared data source 'dsMyObject' for the report server or SharePoint is not valid. Browse to the server or site and select a shared data source. (rsInvalidDataReference)"

When I used the exact same credentials in an embedded data source the report runs and after I get it to run I can change it back to the shared data source I created and it runs just fine.

My problem is that this process is too inefficient I have to many reports, and many more servers to roll this too, to have to manually switch the data source back and forth in order for it to work.

I noticed that when I look through the list of data sources I see the ones that I created with the name of the DS on the first line and the location on the next (http://[Server]/reports/[Data Source Folder]) while the DS created by the vender have the name of the DS on both lines. This leads me to believe that there is a way to load the DS into SSRS with the same name the vendor used in such a way to allow the reports to automatically use them. I can’t find anything that tells me how to do this so I am asking this question here. How do I get prewritten reports to see a shared data source on the server I deploy the reports to?

Thanks in advance,

Answer 1

I know this is a really old question but I came across this as I was facing a similar issue over the past two days. However, in my experience the solution was simpler - so I'll add it in case someone else searches for this in the future.

You need to create your data source before you deploy your reports into RS. Here's what happened for me:

1) I first deployed them before adding a data source, and they didn't work: neither from the application that I'm workin on, nor from RS

2) I then created a data source but that didn't do anything to existing reports. I was able to configure some of them and point them to the new data source, and that worked. If you want to do this in bulk, you could probably use the RS Utility as suggested above or probably something like this, however I haven't tried these.

3) I tried redeploying the reports into RS, however apparently RS doesn't overwrite items that haven't changed as the redeployed reports still didn't pick up the datasource

4) I then deleted existing reports and redeployed - this fixed it and they picked up the data source straight away.

Answer 2

This is a big shortcoming of SSRS. When reports and data sources are created in SSRS, they are assigned unique identifiers in the ReportServer database. When reports are uploaded, they do not recognize an existing data source with the same name the report was built with.

One way around this is to use the rs.exe utility that comes with SSRS. It can be used for bulk tasks, including setting data source metadata on reports. See the link below for more technical information.

RS Utility