Click here to Skip to main content
12,823,954 members (42,954 online)
Click here to Skip to main content
Add your own
alternative version


10 bookmarked
Posted 23 Oct 2011

SSRS Multi-Data Source DPE (Data Processing Extension)

, 23 Oct 2011 CPOL
Rate this:
Please Sign up or sign in to vote.
A way to combine data from two different sources into one datasource for SSRS reporting


SSRS is a wonderful tool for quickly retrieving data from many different data sources and presenting the data to the user at a run-time decided format. One area where SSRS often falls short is when the underlying data needs to come from several different sources. Perhaps we want to retrieve data from the General Ledger which is in Oracle, and join that against employees which are listed in SQL Server to display in a table. Many times when this happens, we end up with two disconnected data sets and are unable to join them without the use of a linked server.

The option shown here is a custom Data Processing Extension that will collect the data from the two different sources and merge them into one table.


Before attempting a custom DPE (Data Processing Extension) for this problem, remember that alternatives such as Web Services, and SSIS data migration might be easier ways for you to stage your data into one data set for reporting purposes. I propose this as one more tool for your tool belt when developing reports against enterprise size systems.

I strongly recommend also reading the following code project tutorial: here to get a stronger understanding of what can be done with an SSRS DPE. I have chosen to upload this tutorial because the current tutorial has significantly more functionality than is needed to implement a basic DPE, and I wanted to trim down some of the additional functionality. The above DPE is used to allow XML files to be a Data Source for SSRS reporting - I highly recommend it to anybody interested in more information on DPEs.

I also highly recommend reading on MSDN the overview of Data Processing Extensions to understand their role within the SSRS lifecycle: Data Processing Extensions Overview.

Using the Code

After downloading the (Data Base Data Processing Extension) and loading it into Visual Studio 2010, you will notice a dependency on Microsoft.ReportingServices.Interfaces. When you install the SQL Server Reporting Services Client on your machine, you should notice a folder:

[SQL Server Path]\MSRS[Version]\ReportServer\bin\ 

On my Denali Instance, that reads:

D:\Program Files\Microsoft SQL Server\MSRS11.DENALI\ReportServer\bin\

This is where Microsoft.ReportingServices.Interface.DLL resides.

It is also important to address .NET versions: SQL Server 2005 and 2008 were built against the 2.0 Framework. 2008 R2 and Denali (SQL Server 2012) are built with the 3.5 assemblies (which we know to be 2.0 along with the handful of extra features). For this reason, we cannot build our DPE as a .NET 4.0 assembly. - Depending on the version of SQL Server, We must choose a version between 2.0 and 3.5. Attempting to use a 4.0 build will cause some very interesting errors.

The two most complex custom objects in the project are DBConnection and DBCommand. The two are designed to utilize two separate data contexts, with their own connections and command text attributes. To merge the two together so that we expose only one command and connection to SSRS, we assume an XML-like syntax to the properties which we will see closer when we look at the RDL file.

The DBCommand object parses the XML file into its two properties as shown below:

        /// <span class="code-SummaryComment"><summary>

The goal of the above code is to merge two potential connection strings into one exposed property. Any exceptions thrown will be handled by the reporting environment (either BI Developer Studio's report designer, or the SQL Server Report Server). These exceptions can then be resolved by the person developing the report.

Once we have the two different connections, we want to execute our two different commands against those connections. A similar strategy is used to combine two commands into one exposed SSRS property.

/// <span class="code-SummaryComment"><summary>

The strategy here is to allow a simulated "left join", where the left and right queries will return two data sets, and they will be joined on a property found in the left table against a property found in the right table. At this point, we only want to parse our the command text, but will use that text later to access the underlying data stores.

/// <span class="code-SummaryComment"><summary>

Here, we will execute the provided connections and commands. The DBConnection object is responsible for opening and closing the connection, so this code is only interested in setting up our command objects. The MergeTables function below will combine our two separate data sets into one data set for our report. The m_dataTable property is shared with the underlying DBDataReader to return data to the reporting environment.

/// <span class="code-SummaryComment"><summary>

I would strongly suggest optimizing the above match algorithm in a production environment - My goal here was straightforward code, so caching and other strategies for optimizing run time are not being used.

Once you have managed to build the Data Processing Extension, remember to deploy it to your development and reporting environments, and to modify the appropriate configuration files. I strongly recommend reading the appropriate MSDN tutorials How to: Deploy a Data Processing Extension to a Report Server and How to: Deploy a Data Processing Extension to Report Designer

Likewise, the code Project Article referenced earlier Implementing a Data Processing Extension is an excellent tutorial for setting up a DPE within the development and reporting environments.

Once you have your reporting environments set up, it is time to load up the Reporting Services project within

The only report within the project is DoubleDB.rdl, and that is sample report using our new Data Source. Open the report and look at the Data Sources defined within the rdl.

Sample Image - maximum width is 600 pixels

The data source type should read "Custom DB Activity Extension", which comes from the LocalizedName property of our DBConnection object. The connection string property should be an XML element (named xml) with two attributes: sqlConn1 and sqlConn2. In our example, they are pointed at the same database, which is a trivial implementation - in a true implementation, we would expect these to be two different database connections.

Sample Image - maximum width is 600 pixels

The DataSet for the previously defined DataSource has a query again which consists of one XML element. Our four attributes are what we defined in our DPE to allow two different queries and a way to join those queries against each other. Clicking refresh fields will validate that everything is acting as expected. I would strongly recommend spending some time debugging your DPE to watch the flow of events.

Sample Image - maximum width is 600 pixels

With the DoubleDBReportProject open in Visual Studio (BIDS), open a second instance of Visual Studio, and load the DBDPE solution. From the DBDPE solution, click Debug -> Attach to Process. In the list of processes on your machine, you should see devenv.exe listed as a process. Select the appropriate devenv.exe instance, and click attach. Adding breakpoints to your DBDPE solution, you should be able to watch the process move from connection to command to data reader and back again to the reporting environment.

Good luck!


  • First version: 10/22/2011


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Software Developer (Senior) Superior Consulting Services
United States United States
Jeff Krebsbach has been a Consultant with Superior Consulting Services (SCS) since 2006. SCS is based in Burnsville, Minnesota and focuses on developing solutions for Microsoft technologies, with a strong set of experience with MS SQL Server, SSRS, SSIS, and SSAS.

You may also be interested in...


Comments and Discussions

QuestionXML file Pin
Member 100904565-Jun-13 12:05
memberMember 100904565-Jun-13 12:05 
GeneralMy vote of 5 Pin
Andrew Giltrap28-Mar-13 7:17
memberAndrew Giltrap28-Mar-13 7:17 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.170308.1 | Last Updated 23 Oct 2011
Article Copyright 2011 by jkrebsbach
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid