How can I change the source of a Data Source View and the Report Models based on it to a different database?

Question

I have a number of reports deployed to a SQL Server 2005 Reporting Services server. They were all developed using the same Report Model (SDML) that references the same Data Source View (DSV) that points to a test database filled with mostly dummy data. Now, I would like to make those reports pull data from the live database with our real data instead. The two databases have exactly the same structure.

It seems to me, that if I could just change the Data Source being referenced in the Data Source View, then I could redeploy the report model, and all the reports based on it would also reference the correct data. I can see in Business Intelligence Development Studio 2005 that there's an option in the Data Source View property list in Design mode to change the Data Source. So I changed the Data Source, thinking that would work. However, when I try to redeploy the report model after changing the Data Source in the Data Source View, I get a number of error messages like this one:

Error 1 The Table property of the Entity 'Address' refers to the Table 'dbo_address', which is not in the primary data source. Events.smdl 0 0

Is there something else I need to be doing here? Something in the Report Model or Data Source View that should be updated? Is there another way to do what I need to?

Edit 1:

I tried changing the datasource of the report model on the server after the reports were deployed, and that seemed to work pretty well. It's not exactly what I wanted to do, but it works. Thanks everyone.

Answer 1

I don't have as much experience with the report models but generally SSRS doesn't like it when you make changes to the datasource and asks you to refresh all the datasets that you have if you do.

Alternatively, just change the datasource definition on the report server itself.

Answer 2

It sounds like you're changing the data source that the dsv references. Instead, why don't you try to change connection string of the data source. Internally the DSV uses GUIDs to identify the various tables and fields, I suspect that by creating a new data source the GUIDs will change and that is why you're seeing these error messages (as the error message is mapping the internally used GUID to it's "friendly name").

Answer 3

The strategy that has worked best for me is to deploy the "test" shared datasource to the server then edit it via the Report Manager interface to point to the "production" database (changing the connection string). Making sure of course Overwrite Datasources is set to false on deploy.

Also, your database schema must be the same in test as it is in production.

Answer 4

The setup I use has an identically named Data Source (.rds) file for each environment, in the same folder the reports are deployed to. It's just a connection string...

My experience has been the same as zalzaw's - if you change the Data Source, you have to refresh all the datasets associated with the report while pointing at the new environment based on the data source changes. It's very tedious - you go to the Data tab for the report in Business Intelligence Development Studio 2005:

1. Select a Dataset from the dropdown menu
2. Click the Refresh button (2nd to the right of the Dataset dropdown, icon looks like recycle)

Repeat steps until all datasets have been refreshed.

Make sure that the database(s) (and stored procedures) are in sync. It's all for naught if a table exists in Dev but not in Test or Prod...