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
deanlee17Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ValentinoVBI ConsultantCommented:
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)
0
Vikas GargBusiness Intelligence DeveloperCommented:
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.
0
ValentinoVBI ConsultantCommented:
Vikas: please read other comments before posting!! You repeated what I mentioned 40 minutes earlier.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

deanlee17Author Commented:
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
0
ValentinoVBI ConsultantCommented:
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!
0
deanlee17Author Commented:
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
0
ValentinoVBI ConsultantCommented:
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)
0
deanlee17Author Commented:
Thank you for the reply. I clicked the link but....'This webpage is not available'
0
deanlee17Author Commented:
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
0
ValentinoVBI ConsultantCommented:
The link was indeed broken, I've fixed it now...

The Report Manager is a website that ships with SSRS and that's what you need to use to configure things like access rights, and shared data sources (unless you deploy them from VS/BIDS).

The URL goes like this: http://yourreportserver/reports

Details: Report Manager (SSRS Native Mode)

The SSRS Config Manager which you mentioned also has a page for this btw, it needs to be set up right before you can access it.  But I understand you're already using that report server, it's not a new one?  So it's probably already set up as it should be.  The Config Manager will tell you though...

See also: Configure Report Manager (Native Mode)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SSRS

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.