Changing the DataSource at Runtime

I have to create a script for changing the datasource at runtime.

Here is my screnario, While development I am using Data source name called "DevDatasource1" and when I am deploying it to other evnironment the datasource name will change let us say "QADatasource".

I have to create a script for changing the datasource(i.e. DevDatasource1 to QADatasource). How I can achieve I this using the setItemdatasource?

You can also set a parameter in your report to accept the specific database or source and use this parameter within your datasource connection string.

Within your report:

My connection string looks like this: ="Data Source=nt33;Initial Catalog=" & Parameters!Database.Value & ";"

This allows me to change the database on the fly, but could change the entire connection string including server.

Location for the code would be Data Tab --> Dataset properties --> Datasource editor --> Connection String:

The bonus is that no matter where the report is run you have the ability to change the datasource.

My parameter is setup to load al list of available databases for the user.

Not what you were looking for specifcally, but it might help.

|||

hi simles

thanks for your reply.

Here is my problem. While deploying my reports on the other server we don't want to create the datasource. Already in the deployment server datasources are created.

what we have to do his while deploying the reports we have to map the existing datasources to the deploying rdl files. Can you explain me how we can achieve this using reports scripts.

|||

So, you have a pre-existing datasource on the server, and you want your newly-published report to use IT instead of the datasource the report was created with, right?

If so, try SetItemDataSources():

http://msdn2.microsoft.com/ru-ru/library/microsoft.wssux.reportingserviceswebservice.rsmanagementservice2005.reportingservice2005.setitemdatasources.aspx

|||

Russell,

Thanks for your reply.

I tried with that setitemdatasources, but it not setting the new datasource name for the rdl.

I don't know were i am making the mistake. please look into the code and correct me if the code is wrong.

Dim dataSources() As DataSource

dataSources = rs.GetItemDataSources("/Reports/Test Matrix")

Dim NewdataSources As new DataSource

Dim dsref As New DataSourceReference

dsref.Reference = "/Data Sources/TestDatasource"

NewdataSources.Item = CType(dsref,DataSourceDefinitionOrReference)

NewdataSources.Name = "NewDatasource"

dataSources(0) = NewdataSources

rs.SetItemDataSources("/Reports/Test Matrix", dataSources)

Simran Raj

sql