SSRS - Upload Data Source after Report

Question

I'm creating an MVC application which uses the Reporting Web service (2010) for programatically managing reports and data sources.

About a month or so ago when I first implemented this functionality, I was able to upload reports (.rdl files) first and later upload its data source. I was then able to view the report using a report viewer control in a web page.

However, since a week or so, this flow has broken, i.e. if I upload the report first and then the data source, the report doesn't render in the report viewer control. It gives the following error.

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

The data source is a shared data source which is defined in the rdl file as follows.

<DataSources>
  <DataSource Name="AW">
    <DataSourceReference>AW</DataSourceReference>
  </DataSource>
</DataSources>

If I reverse the flow, i.e. upload the data source first and then the report, it starts working! But I'm 100% sure, the other flow used to work when I first implemented it.

I'm stumped as to why the original flow has stopped working. Both the report and the data source are uploaded to a specific folder.

Can someone please shed some light on this. Does the original flow make sense? I mean is it supposed to work, or was I imagining stuff?

btw, the data source uploaded is in the following format

<?xml version="1.0" encoding="utf-8"?>
<DataSourceDefinition xmlns="http://schemas.microsoft.com/sqlserver/reporting/2006/03/reportdatasource">
  <Extension>SQLAZURE</Extension>
  <ConnectString>Data Source=xxx;Initial Catalog=AdventureWorks2012</ConnectString>
  <UseOriginalConnectString>false</UseOriginalConnectString>
  <OriginalConnectStringExpressionBased>false</OriginalConnectStringExpressionBased>
  <CredentialRetrieval>Store</CredentialRetrieval>
  <WindowsCredentials>false</WindowsCredentials>
  <ImpersonateUser>false</ImpersonateUser>
  <UserName>user</UserName>
  <Password>pass</Password>
  <Enabled>True</Enabled>
</DataSourceDefinition>

and I use the ReportingService2010.CreateCatalogItem method for creating both the report and the data source.

Any help is highly appreciated.

I don't know about using the web service programatically but when designing reports and deploying to a report server using Visual Studio, attempting to deploy a report when the data source doesn't exist will result in an error. It wouldn't make much sense to allow uploading reports when the dependent data source didn't exist, otherwise RS would have to implement logic to update the report metadata after you uploaded the data source. Perhaps previously you were not deleting the data source between uploads? — Nathan Griffiths
When I upload a report using the web service, I get a rsDataSourceReferenceNotPublished warning. I repeat, its a warning, not an error. So by design, it seems this should be okay. Maybe I'm missing a step somewhere. But then I wonder how and why was it working previously. — Vishal Shah
According to MSDN rsDataSourceReferenceNotPublished is a non-critical error. I can't explain why this appeared to work previously but it seems to me that for this approach to work you would need to fix up the data source reference after the report has been deployed by using the GetItemDataSources and SetItemDataSources methods. — Nathan Griffiths
I tried, that but it gave me a different error. I don't have the exact error right now, but it was something on the lines that the data source does not exist. I guess it might work if I tried to fix the data source reference after I upload the data source. But that defeats the purpose I guess. — Vishal Shah

Eugene Khrustalev Eugene Khrustalev · Accepted Answer · 2016-06-29T10:29:56

Perhaps, my case may be useful for you. I had a task to upload reports from rdl-files to SSRS2012 before application server start. I decided to use Web Service API, not rs.exr. After report loading by method CreateCatalogItem of class ReportingService2010 I tried to execute reports in browser and got error. SSRS could not find shared data source for loaded report. When I published reports from VS2013, reports worked properly. I could not use method SetItemDataSources , because could not get the reference to existing data source as instance of DataSource class. That is why I found following solution. The idea in that the rdl-file has only name of shared data source, not path. Therefore, if report and its data source are located in different folder, report don’t see data source. In my case my data source located in folder “Data Sources”, so it’s necessary to correct rdl-file before uploading,, as shown below:

Warning[] warnings = null;

string name = "ProductList";

string dsName = "DB_CORE";

string dsFolderPath = "/Data Sources";

byte[] definition = null;

// correct rdl

XmlDocument rdlDoc = new XmlDocument();

rdlDoc.Load(+ name + ".rdl");

XmlNodeList dsRefmodes = rdlDoc.GetElementsByTagName("DataSourceReference");

dsRefmodes[0].InnerText  = dsFolderPath + "/" + dsName;

definition =  Encoding.UTF8.GetBytes(rdlDoc.OuterXml);

// upload

CatalogItem report = rs.CreateCatalogItem("Report", name, "/" + parent,
                            true, definition, null, out warnings);

SSRS - Upload Data Source after Report

1 Answers