Thursday, May 20, 2010

Dynamic Connection Strings With SSRS Data Processing Extension

It is my firm opinion that whoever came up with the names for various parts of SQL Server must be fired. "SQL Server Reporting Services", "SQL Server Analysis Services", "SQL Server Service Broker" do not exactly roll off the tongue. Just try using a few of these in a speech - you will immediately realize you need nicknames (SSRS sounds too much like USSR. Oh, well, I'll just call it Kevin). Anyway, this post wasn't supposed to be a rant, so let's move on.

SSRS supports ten data sources out of the box, including SQL Server (duh!), ODBC, OLE DB, and Oracle. Data Processing Extensions are usually recommended when you need to generate reports from non-standard data sources, for example, files in a proprietary format. You start by creating a .NET assembly with classes that implement a half-dozen or so interfaces defined in Microsoft.ReportingServices.Interfaces.dll. Once everything is working, you deploy the assembly to two separate locations: Visual Studio subfolder on a report developer's workstation, and a Reporting Services subfolder on a server. The process is well documented on MSDN and there is also a good article on The Code Project.

However, what if your database is a SQL Server, but you cannot rely on a static connection string? Suppose you maintain separate databases for your various customers and generate connection strings at runtime? Although SSRS allows us to use parameterized connection strings, sometimes this isn't an optimal solution, given the fact that those parameters are passed around openly inside URL. I found that Data Processing Extensions can be used very effectively in this scenario.

Rather than implementing all the interfaces required by DPE, we will encapsulate an existing class SqlConnectionWrapper in the Microsoft.ReportingServices.DataExtensions namespace (it is marked as "sealed", so you can't subclass it):

   1:  using Microsoft.ReportingServices.DataExtensions;
   2:  using Microsoft.ReportingServices.DataProcessing;
   4:  public class MySqlConnection : IDbConnectionExtension
   5:  {
   6:      private SqlConnectionWrapper _Connection;
   8:      public MySqlConnection()
   9:      {
  10:          _Connection = new SqlConnectionWrapper();
  11:      }
  12:  }
Right-click IDbConnectionExtension and choose "Implement Interface". This automatically implements three more interfaces, IDbConnection, IDisposable, and IExtension members. Most of the new methods and properties added to our class will be merely wrappers of the respected methods and properties of the _Connection. For example:
   1:      public string Password
   2:      {
   3:          set { _Connection.Password = value; }
   4:      }
   6:      public string UserName
   7:      {
   8:          set { _Connection.UserName = value; }
   9:      }
Of course, you still need to add the implementation.

Property LocalizedName should return the string that you want report developers to see in the list of data sources (e.g., on the "Select Data Source" screen of the new report wizard).
   1:      public string LocalizedName
   2:      {
   3:          get { return "Dynamic SQL Server Connection"; }
   4:      }
Arguably the most important implementation is ConnectionString setter. This is where you need to put your proprietary logic that dynamically generates a valid connection string. There are a couple of different approaches. If you don't need any additional information in order to generate the connection string, ignore the "value" and just call necessary methods:
   1:      public string ConnectionString
   2:      {
   3:          get
   4:          {
   5:              return _Connection.ConnectionString;
   6:          }
   7:          set
   8:          {
   9:              _Connection.ConnectionString = MyDataLayer.GenerateConnectionString();
  10:          }
  11:      }
If, on the other hand, your logic does require parameters, you will need to parse ConnectionString value that client code provided. Example below uses regular expression to extract CustomerID from the value (and skips property getter for brevity):
   1:      public string ConnectionString
   2:      {
   3:          set
   4:          {
   5:              Match m = Regex.Match(value, "CustomerID=([^;]+)", RegexOptions.IgnoreCase);
   6:              int custId = 0;
   7:              if (m.Success
   8:                 && int.TryParse(m.Groups[1].Captures[0].Value, out custId))
   9:              {
  10:                  _Connection.ConnectionString = MyDataLayer.GenerateConnectionString(custId);
  11:              }
  12:              else
  13:              {
  14:                  throw new ArgumentException("Valid CustomerID is missing");
  15:              }
  16:          }
  17:      }
Now the only thing left to do is deploy assembly containing MySqlConnection to both server and developer workstation.

*** UPDATED 11/22/2011 ****

Bad news for those of you using SQL 2008 R2 - the approach of encapsulating Microsoft.ReportingServices.DataExtensions.SqlConnectionWrapper I outlined in this post no longer works. Apparently, they changed class accessibility from "public" to "internal". Looks like the only opportunity is to implement all interfaces manually.


Richard said...

Have you modified this approach to work with 2008 R2?

Riyad Mammadov said...

No, I'm afraid I don't have any updates for SQL 2008 R2.

SataMaster said...

Any help for a 2008 R2 compatible code version?

I am getting error while using the SqlConnectionWrapper.

And i am a bit lost here.

Thank you

Riyad Mammadov said...

I finally installed SSRS 2008 R2 and tried to recompile the code with new libraries. Alas, it didn't work. Please see update appended to the original post.

Unknown said...

Hi I have one requirement:
I have 20 databases for 20 users, all are having the same schema.Instead of creating 20 models,i want to create a report model & 20 data sources.But the problem here is Report model is tightly coupled with single data source. Is there any way to change the connection string of data source dynamically so that every user get his data?

Anis said...

I have a problem with Connection Strings With SSRS 2008 R2 Data Processing Extension.
My problem is at the connection string in report designer.
I can not seem to connect to my database.
it makes me the following error:
Unable to connect to data source 'DataSource1'.'FileNamed=' must be present in the connection string and point to a valid DataSet xml file
Parameter name: Data Source=EVE1204001;Initial Catalog=DataSource1