Using Dynamic Connection Strings in SSRS2008

2
votes

I have a bunch of SSRS 2008 reports which I'd like to run on several different machines (development, test, production). Each machine has it's own database, so I need to use different connection strings depending on where the report is running.

One workaround I found is to specify the server and catalog name in a hidden parameter that is passed to the report at runtime. It's described in this tutorial, but it applies to SSRS 2005 and I could not make it work in SSRS 2008.

Everything works fine when the connection string in my shared datasource looks like this:

Working Connection String

Just so to see if expressions can be used at all for the connection string, I replaced the connection string with this:

Dynamic but broken connection string

But this gives me the following error when I try to preview the report in Visual Studio:

An error occurred during local report processing. The item '/Zeiterfassung-Adrian' cannot be found.

Are dynamic connection strings still working in SSRS 2008?

If yes, what am I doing wrong?

If not, what else can I do?

6
connection-stringssrs-2008reportingservices-2005reporting-services
Depending on what audience is actually running these reports you might be interested in custom reports in management studio. msdn.microsoft.com/en-us/library/bb153684.aspx - Bob Albright
While I know this question is a little old, what version of BIDS is the screen shot from? My 2008 BIDS has the little "fx" below the Edit button which allows you to do what you are asking about. - D.S.
@D.S.: It's from Visual Studio 2008 Team System. - Adrian Grigore
@D.S. the fx button is located on the "Data Source Properties" window but not on the "Shared Data Source Properties" window. So only embedded data sources can be made dynamic using expressions. - Valentino Vranken

6 Answers

1
votes

Just create your report datasource for each environment and deploy them. Then switch your deploy option to not overwrite a datasource. Though to make for simple deployments you will have to configure the configuration manager in bids for each environment. This is how we work in our multiple environments.

Hope it helps, let me know if you have any questions on this.

2
votes

It's because it's a shared datasource. Should work fine for a regular embedded datasource. Take a look at THIS link for an option on using dynamic connections strings with shared datasources, might be helpful for you.

1
votes

Please check related article at http://haseebmukhtar.wordpress.com/2011/11/09/dynamic-database-in-ssrs-2008/

Also you can not use dynamic database settings for the shared data source.

0
votes

The string should have double double quotes for server name. ="data source="" & Parameters!MyServerParameter.Value & "";initial catalog=DBName.."

0
votes

I was able to create a dynamic embedded connection using a ServerName parameter as follows:

="data source=" & Parameters!ServerName.Value & ";initial catalog=master"

-1
votes

What about using a hidden report parameter?

then you should be able to do:

="data source=" & Parameters!MyServerParameter.Value & ";initial catalog=DBName.."

Here is an article which should help you out: http://msdn.microsoft.com/en-us/library/ms156450.aspx