How can I change the view source of the data source and report model based on this to another database?
I have several reports deployed to a SQL Server 2005 Reporting Services Server. All were developed using the same Reporting Model (SDML) that references the same Data Source (DSV) that points to the test database, which is mostly populated dummy data. Now I would like these reports to pull data from a live database with our real data. The two databases have the same structure.
It seems to me that if I could just change the data source that is being referenced in the Data Source View, then I could redeploy the report model and all reports based on it will also reference the correct data. In Business Intelligence Development Studio 2005, you can see that there is a Change Data Source option in the Data Source Properties list in Design mode. So I changed the data source thinking it would work. However, when I try to redeploy the report model after changing the data source in the data source view, I get several error messages like this:
Error 1 The "Table" property of the "Address" object refers to the "dbo_address" table, which is not in the main data source. Events.smdl 0 0
Is there something else I need to do here? Is there something in the report model or data source view that needs to be updated? Is there any other way to do what I need?
Edit 1:
I tried changing the data source of the report model on the server after the reports were deployed and it looked very good. This is not exactly what I wanted to do, but it works. Thanks everyone.
source to share
The strategy that worked best for me was to deploy the "test" shared data source to the server and then edit it through the Report Manager interface to point to the "production" database (changing the connection string). Making sure, of course, that the Overwrite Datasources is set to false when deploying.
Also, your database schema should be the same as in the testing process.
source to share
It looks like you are changing the datasource that dsv refers to. Instead, why don't you try changing your data source connection string. Internally the DSV uses GUIDs to identify various tables and fields, I suspect that by creating a new datasource the GUIDs will change, which is why you see these error messages (since the error message displays the internally used GUID for it a "friendly name").
source to share
I have an identical Data Source (.rds) file for each environment, in the same folder where the reports are deployed. It's just a connection string ...
My experience was the same as zalzaw - if you change the data source, you must update all the datasets associated with the report to point to the new environment based on the data source changes. This is very tedious - you go to the Data tab for the report in Business Intelligence Development Studio 2005:
- Select dataset from dropdown menu
- Click the Refresh button (second to the right of the Dataset dropdown, the icon looks like a redesigned)
Repeat the steps until all datasets have been updated.
Make sure the database (and stored procedures) are in sync. This is all in vain if the table exists in Dev but not Test or Prod ...
source to share