Sql – How to deploy SQL Reporting 2005 when Data Sources are locked

reporting-servicessql servervisual studio

The DBAs here maintain all SQL Server and SQL Reporting servers. I have a custom developed SQL Reporting 2005 project in Visual Studio that runs fine on my local SQL Database and Reporting instances. I need to deploy to a production server, so I had a folder created on a SQL Reporting 2005 server with permissions to upload files. Normally, a deploy from within Visual Studio is all that is needed to upload the report files.

However, for security purposes, data sources are maintained explicitly by DBAs and stored in a separated locked down common folder on the reporting server. I had them create the data source for me.

When I attempt to deploy from VS, it gives me the error

The item '/Data Sources' already exists.

I get this whether I'm deploying the whole project or just a single report file. I already set OverwriteDataSources=false in the project properties. The TargetServer URL and folder are verified correct.

I suppose I could copy the files manually, but I'd like to be able to deploy from within VS. What could I be doing wrong?

Best Answer

Add a string parameter called ConnectionString to your report and save it. Next, open your RDL in a text editor and change the data source definitions like this. 

  <DataSources>
    <DataSource Name="preserve the datasource name you've been using">
      <ConnectionProperties>
        <DataProvider>SQL</DataProvider>
        <ConnectString>=Parameters!ConnectionString.Value</ConnectString>
      </ConnectionProperties>
      <rd:DataSourceID>preserve your existing GUID</rd:DataSourceID>
    </DataSource>
  </DataSources>

You will now find that you can pass in the database connection string as a report parameter. Be careful not to mention this to your DBAs because there is no provision in the SSRS security system for controlling this and they will go completely nuts when they discover that not only is the cage door open, it can't be closed.

Related Topic