"Shared" Data Sources: behavior within BIDS Report Manager

• mahler9

  Mr or Mrs. 500

  Points: 569

  More actions

  March 16, 2015 at 9:38 am

  #398192

  ** First--apologies, I've posted this to this 2005 forum, it belongs in a 2008 forum or the general reporting services forum. **

  What are best practice approaches managing a "shared data source" when building out report server--in terms of credential and type (SQL Server, OLE DB, ODBC).

  Running 2008 R2. For now, the context is a basic select from a single SQL Server database. I am new to creating new data sources in SSRS, and my experience working with SSRS reports has been limited to using BIDS. The relationship between data sources within BIDS and within Report Manager seem . . . different, and not very straightforward to manage at first glance.

  1. If I create a shared data source, "X", with saved credentials in BIDS Project 1, an X.rds file is created within Project 1's directory structure.

  2. If I create a shared data source, also named "X", with saved credentials in BIDS Project 2, an X.rds file is created within Project 2's directory structure

  3. When Project 1 is deployed an "X" shared data source is created on the report server, visible under Datasources on the Report Manager Home page. When viewed within Report Manager the shared data source for reports in both Project 1 and Project 2 live in /Data Sources/X.

  I am aware of the overwrite datasource option in BIDS, so I know that when I deploy Project 2 I should use the same credentials, as I must deploy the data source for each new project.

  If I edit the credentials for datasource "X" in Report Manager, neither data source within Project 1 or Project 2 is updated.

  • When would I ever want to manage shared data sources in Report Manager? It does not appear to be a two-way street between BIDS and the report server. If I update the data source credentials--or if I try to change to a different shared data source--in Report Manager, data sources are not updated in BIDS.

  • Some suggest copying a "master" *.rds file to each new project's directory as a way to select an "existing" shared data source within BIDS. Really? Ugh.

  Thanks all. Please let me know anything pertinent I've overlooked.