Change SSRS data source of report programmatically in server side

This is something we've done in our environment - we maintain one set of reports that can be deployed at any client with their own configuration.

You've got a couple of options here. Since you're using a Shared Data Source this makes things easier as you won't need to define a Data Source for each report.

1. Use the rs.exe utility and a script file

rs.exe at Books Online

This program allows you to create script files (in VB.NET) that can interact with a Report Server Web Service. You create a script file (e.g. Deploy.rss) and call the rs.exe program with various parameters, including any custom ones you define:

rs.exe -i DeployReports.rss -s http://server/ReportServer -v DatabaseInstance="SQL" -v DatabaseName="ReportDB" -v ReportFolder="ClientReports"

So this would call a script DeployReports.rss, connect to http://server/ReportServer, with three user defined parameters which could be used to create a data source and the report folder.

In the scipt file you could have something like this:

Public Sub Main()    rs.Credentials = System.Net.CredentialCache.DefaultCredentials    CreateFolder(reportFolder, "Report folder")    CreateFolder(datasourceFolder, "Data source folder")    CreateDataSource()End Sub

Which can then make Web Service calls like:

rs.CreateFolder(folderName, "/", Nothing)'Define the data source definition.Dim definition As New DataSourceDefinition()definition.CredentialRetrieval = CredentialRetrievalEnum.Integrateddefinition.ConnectString = "data source=" + DatabaseInstance + ";initial catalog=" + DatabaseNamedefinition.Enabled = Truedefinition.EnabledSpecified = Truedefinition.Extension = "SQL"definition.ImpersonateUser = Falsedefinition.ImpersonateUserSpecified = True'Use the default prompt string.definition.Prompt = Nothingdefinition.WindowsCredentials = FalseTry    rs.CreateDataSource(datasource, datasourcePath, False, definition, Nothing)    Console.WriteLine("Data source {0} created successfully", datasource)Catch e As Exception    Console.WriteLine(e.Message)End Try

You haven't specified what version of Reporting Services you're using, so I'm assuming 2008. Please note that there are multiple endpoints that can be used, depending on SQL Server version. The 2005/2008 end point is deprecated in 2008R2 and above but is still usable. Just something to bear in mind when writing your script.

2. Call the SSRS Web Service through an application

Report Server Web Service overview

The same calls that are made from the script above can be made in any other application, too. So you'd just need to add a reference to a Report Server Web Service through WSDL and you can connect to a remote service and call its methods to deploy reports, data sources, etc.

So ultimately you're connecting to the Report Server Web Service, it's just the medium used that you need to think about.

Using a script is easier to get running as it's just running a program from the command line, but writing your own deployment application will certainly give greater flexibility. I would recommend getting the script going, so you understand the process, then migrate this to a bespoke application if required. Good luck!

You can use an Expression Based Connection String to select the correct database. You can base this on a parameter your application passes in, or the UserId global variable. I do believe you need to configure the unattended execution account for this to work.

Note: be careful about the security implications. Realize that if you would pass sensitive data (e.g. passwords) into a parameter, that (a) it will go over the wire, and (b) will be stored in the execution log tables for reporting services.