CODEWITHSUNDEEP
ssrs-2008
Kenneth
Kenneth

Reputation: 91

SSRS ReportingService2010 change embedded DataSource to shared DataSource

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

Unfortunately, those who came before me embedded the data source information in each report (connection string, login, password, etc). I thought this would be a great time to change these to point to the shared data source so this would not have to be done for each report, one by one. I can create the reports on the new server just fine using CreateCatalogItem but I can't seem to determine how to properly go about changing from an embedded data source to a shared data source.

So far I have tried both 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 attempted on a report with an embedded data source but they both work just fine on reports that are already pointing to a shared data source.

Furthermore, I have searched all over Google as well as StackOverflow for a solution but have found nothing. Can anyone point me in the right direction here?

Upvotes: 4

Views: 3893

Answers (1)

Kenneth
Kenneth

Reputation: 91

So I kept working with the SetItemReferences method and had a brilliant idea that ended up working. The final 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 was not using the same DataSource name as what was found in the report .rdl file. I was able to determine what the name should be using the GetItemDataSources method. Since this method returns an array and may have more than one item in said array, I looped through it to create multiple ItemReferences if more than one existed though I doubt that happens very often if at all.

Upvotes: 5

Related Questions