SSRS SharedReportDataSource Problem

Kinetic ERP
1

I am in the process of upgrading to 2021.2. My company has 2 separate environments, one for our US operations and one for our European operations. I upgraded the US environment about a month ago without issue and am working on getting our EU environment upgraded now. Some months ago I got our Kinetic server up and going so we could start testing. So far everything has been great. I noticed that I missed the “SSRS Reports Root Folder” when I deployed it, so I wanted to fix that. I changed that and re-deployed, then used Powershell to move all my reports into the new root folder, also changing the Datasource path on each report in the process. Well, none of the reports worked. I keep getting the following error:

Program Ice.Services.Lib.RunTask raised an unexpected exception with the following message: RunTask:
Ice.Core.SsrsReportService.ReportExecutionSoapException: Exception of type ‘Ice.Core.SsrsReportService.ReportExecutionSoapException’ was thrown.
SOAP Fault: The report server cannot process the report or shared dataset. The shared data source ‘dsPackSlip101’ for the report server is not valid. Browse to the server or site and select a shared data source. —> Microsoft.ReportingServices.Diagnostics.Utilities.InvalidDataSourceReferenceException: The report server cannot process the report or shared dataset. The shared data source ‘dsPackSlip101’ for the report server is not valid. Browse to the server or site and select a shared data source.)

So I went back and made sure that my path was correct. It was. I figured I would try re-importing all the stock reports. So I did that and that fixed the default reports but my custom reports still aren’t working. I compared the datasource connection in the RDL and they are the same. I then tried changing the datasource in Report Builder. What do you know, that worked! I have no idea why, but it worked. I downloaded one of my custom reports, made the datasource change in Report Builder, then redownloaded the report so I could compare them.

Contents are identical.

What the heck am I missing here? I have done this before without issue… :tired_face:

2

There is a shared data source that is not a report.

It’s in the root folder for all your reports, did that come over?

3

image

This should be located in the “reports” folder for whatever environment you are in. It will be at the bottom under data sources.

4

Yep, that is in there. That’s what everything is pointing to. And… I just figured it out.

SSRS stores the data source in the Catalog table (as it does everything else, no surprise here). However, I found that it also stores each data source in it’s own separate table. So, it looks like it doesn’t just read the data source from the RDL, it’s pulling from it’s own DB records. It looks like because I recreated the shared data source, it got a new ID. All my old reports, although the path was correct, had the wrong ID in it. Updated the IDs and it’s working!

What a fun way to spend the day.

Report Data Sources - From the individual SSRS Reports

image
image1920×439 138 KB

Shared Data Sources

image
image1084×297 113 KB

So back to my original post, the “Link” column for my reports after I moved them over was incorrect. When I was updating the reports in Report Builder, it was correcting that in the database. Seems odd that it does it this way even though the RDL has the path in it.

5

Glad you got it resolved. Without getting into the SQL database this is all I could think of.

Makes sense that something was saved in SQL that was different than the report.

Odd that your powershell script and transfer didn’t update the link though.