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.

Answer 1

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);

Answer 2

you can also use a SQL query against the reporting services database to correct the error

--to bulk update the data source for SSRS reports run this script

--USE REPORTSERVER

--Update one report then run this and copy the DS.Link

      SELECT C.Name, C.path, ds.name, ds.Link
FROM   DataSource    AS ds
      JOIN CATALOG  AS c ON  c.ItemID = ds.ItemID
WHERE  ds.NAME = 'HALoCOREDB' AND c.path LIKE '%/Reports/%' AND ds.Link IS  NOT NULL

--Update the DataSource for Reports

BEGIN TRAN

UPDATE dSource set [Flags] = [Flags] | 2, [Link] = '53EFEDAE-3485-450A-AE4D-93AF1E0C4684'
FROM   DataSource    AS dSource
      JOIN CATALOG  AS cLog ON  cLog.ItemID = dSource.ItemID
WHERE  dSource.flags = dSource.flags AND dSource.Link IS NULL AND dSource.ConnectionString IS NULL 
       AND dSource.NAME <> 'HALoCOREDB' AND cLog.path LIKE '%/Reports/%'

--Verify the tRansactiuon

      SELECT C.Name, C.path, ds.name, ds.Link
FROM   DataSource    AS ds
      JOIN CATALOG  AS c ON  c.ItemID = ds.ItemID
WHERE  ds.NAME = 'HALoCOREDB' AND c.path LIKE '%/Reports/%' AND ds.Link IS  NOT NULL

--Commit the Transaction once verified

Commit Tran