SSRS ReportingService2010 changes the embedded DataSource to a shared DataSource

Question

I have SQL Server 2008 with SSRS installed on a single server, and SQL Server 2008 R2 with SSRS installed on a new server. I want to migrate 200+ reports, as well as several shared schedules and a couple of data sources from the first server to the second, using the SSRS web service API. For simplicity, since there are only a few common data sources, I went ahead and created those that use the report manager interface.

Unfortunately, those who came before me included information about data sources in each report (connection string, login, password, etc.). I thought it would be a great time to change them to point to a common data source, so this would not have to be done for each report one by one. I can create reports on the new server just fine using CreateCatalogItem, but I can’t determine how to properly switch from the built-in data source to the shared data source.

So far I have tried and SetItemReferences:

itemRef.Reference = "/Data Sources/TMS"; itemRef.Name = "TMS"; itemRef.Reference = "/Data Sources/TMS"; rs2010.SetItemReferences(catItem.Path, new ReportService2010.ItemReference[] { itemRef }); 

and SetItemDataSources:

 ReportService2010.DataSourceReference dataSourceRef = new ReportService2010.DataSourceReference(); dataSourceRef.Reference = "/Data Sources/TMS"; ReportService2010.DataSource dataSource = new ReportService2010.DataSource(); dataSource.Name = "TMS"; dataSource.Item = dataSourceRef; rs2010.SetItemDataSources(catItem.Path, new ReportService2010.DataSource[] { dataSource }); 

Both methods result in a “NotFoundException” when trying to report with an embedded data source, but both of them work fine in reports that already point to a common data source.

In addition, I searched all over Google, as well as StackOverflow for a solution, but found nothing. Can someone point me in the right direction?

Answer 1

Therefore, I continued to work with the SetItemReferences method and had a brilliant idea that ended up working. The last code I used is below:

 List<ReportService2010.ItemReference> itemRefs = new List<ReportService2010.ItemReference>(); ReportService2010.DataSource[] itemDataSources = rs2010.GetItemDataSources(catItem.Path); foreach (ReportService2010.DataSource itemDataSource in itemDataSources) { ReportService2010.ItemReference itemRef = new ReportService2010.ItemReference(); itemRef.Name = itemDataSource.Name; itemRef.Reference = "/Data Sources/TMS"; itemRefs.Add(itemRef); } rs2010.SetItemReferences(catItem.Path, itemRefs.ToArray()); 

The problem was that I did not use the same DataSource name as in the .rdl report file. I was able to determine which name the GetItemDataSources method should use. Since this method returns an array and can contain more than one element in the specified array, I looped it to create several ItemReferences, if there were more than one, although I doubt that this happens very often, if at all.