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;

   3:   

   4:  public class MySqlConnection : IDbConnectionExtension

   5:  {

   6:      private SqlConnectionWrapper _Connection;

   7:   

   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:      }

   5:   

   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.