Link to home
Start Free TrialLog in
Avatar of deanlee17
deanlee17

asked on

Create dynamic SSRS connection string

Hi guys,

When we deploy a new app it would generally go through three stages...

Development > Testing > Deployment.

Lets say for argument sake they are three different servers. The current SSRS reports has static connection strings, so when something is moved from Development to Testing the connection strings need to be changed for all 20 reports. The change to the connection string would be the server name each time, is there a way to make this dynamic?

Thanks,
Dean
Avatar of ValentinoV
ValentinoV
Flag of Belgium image

You should use a Shared Data Source as opposed to each report having its own embedded data source.  This way you just need to create it once on each environment and all reports can make use of it.

Details: Create, Modify, and Delete Shared Data Sources (SSRS)
Hi,

You can use Shared Data Source which will be used for the reports as data source.

And when you change server you just need to change the connection string of the at single shared Data Source.
Vikas: please read other comments before posting!! You repeated what I mentioned 40 minutes earlier.
Avatar of deanlee17
deanlee17

ASKER

Hi guys,

Thanks for the replies. So am i correct in thinking this shared data source can have multiple datasets? Also where exactly would the data source sit? as i'm assuming it is not within a report itself? Sorry if these are basic questions.

Thanks,
Dean
A dataset uses a shared data sources, which means it just refers to it so that it knows on what server/DB the query should get executed.

What are you using to build your reports?  Business Intelligence Development Studio (Visual Studio) or Report Builder?

In BIDS, a shared data source is created in the project, similar to creating a report.  Select "data source" instead of "report" in the Add new item popup window.

In Report Builder I don't think you have this option so you would need to create it directly on the server using the Report Manager.

There's a detailed explanation here: Create an Embedded or Shared Data Source (SSRS)

Let me know if something on that page is not clear, I'll try to clarify then!
Hi,

I am indeed using visual studio.

I have two databases setup. I have a project setup with 1 data source pointing to database 1 and 1 dataset looking at the datasource and 1 table displaying the results. This works fine, if I go into the Datasource and change the connection string then I get the data from database 2. So this all works perfectly.
However the datasource is inside that particular project. If I started a completely different project can I still access that datasource in some way? Reason I ask is because we have multiple projects that ideally need to connect to the same datasource.

Thanks,
Dean
Hmm, in that case you can best set the data source up in a general "/data sources" folder so that all reports can reference it.  I'm talking about the location on the report server here.

Just so you know, if you deploy your projects through Visual Studio you can specify where the shared data sources should go.  This is specified separate from the actual reports folder.

More details: How to: Set Deployment Properties (Reporting Services)
Thank you for the reply. I clicked the link but....'This webpage is not available'
Ok i'm a bit lost with this. I remote onto the server, I can find report services config manager and that seems to be setup and running. I cant see where the data sources folder sits? Also where would you create the Shared Data Source if not inside Visual Studio to then push it to that folder?

I'm clearly missing something simple here.

Thanks,
Dean
ASKER CERTIFIED SOLUTION
Avatar of ValentinoV
ValentinoV
Flag of Belgium image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial