Click here to Skip to main content
15,867,141 members
Articles / Database Development / SQL Server
Article

Implementing a Data Processing Extension

Rate me:
Please Sign up or sign in to vote.
4.65/5 (10 votes)
16 Jan 2008CPOL4 min read 80.7K   2.6K   52   15
I will take you through how to deploy your own Custom Data Process Extension for SQL Server Reporting Server 2005, both on the server and for use within your development environment.

Custom Data Source Extension

Introduction

I will take you through how to deploy your own Custom Data Process Extension for SQL Server Reporting Server 2005, both on the server and for use within your development environment.

Background

I've been using and writing Reports for SQL Server Reporting Services for some time now, and it has come time to write my own Security Implementation for the reports. In order for me to do this, I needed to find out how to change the Data Sources within Reporting Services.

I did find some resource information on Microsoft TechNet: Implementing a Data Processing Extension. I found this article both useful and hard to follow, hence I have written this CodeProject article to help you get up and going as quickly as possible.

Prerequisites

  • I assume you are familiar with SQL Reporting Services, what they are, design, and installation. For more info about this subject, click here.
  • You should have SQL Reporting Services 2005 already installed.
  • Visual Studio 2005 with SQL Server Reporting Services Report Templates installed.

Compile the Custom Data Source Extension

After loading the source code, you will need to reference the "Reporting Services Interfaces". In my case, it is located in: C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies\Microsoft.ReportingServices.Interfaces.dll; however, this may differ for your installation.

Once you have made the reference, you should be able to compile the application.

Installation of the Custom Data Source

After downloading the source above for the custom data processing extension, you must install it to enable access. The installation process is performed in two steps:

  • Installing and configuring the extension.
  • Configuring extension security.

This particular extension is used both by the Reporting Server and within the Visual Studio 2005 Report Designer; both have a different location for configuration, either on the server or your development machine. However both configurations are very similar.

Development Installation

Two configuration files are required to be changed in order for you to be able to see your Custom Data Source from within the development environment. These files typically are held in C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies; these files are:

  • RSReportDesigner.config
  • RSPreviewPolicy.config

Copy your assembly

First, you need to copy the Custom Data Source files to the "PrivateAssemblies" folder, as this is where they are referenced.

Setup the configuration file

The RSReportDesigner.config holds the reference to the Extension. The following needs to be added to the <Data> tag within the <Extension> tag:

XML
<Extension Name="CUSTOM_DATASOURCE" 
   Type="DataSourceExtension.DataSetConnection, DataSourceExtension" />

In order for the designer to use the Custom Data Source, you need to add the following to the <Designer> tag, usually situated just below the <Data> tag:

VB
<Extension Name="CUSTOM_DATASOURCE" _
 Type="Microsoft.ReportingServices.QueryDesigners.GenericQueryDesigner,
       Microsoft.ReportingServices.QueryDesigners"/>

You will notice that this time we are pointing to the standard Microsoft Generic Query Designer. This is because we are trying to build a Custom Data Source Extension and not a Query Designer.

What you should end up with is a RSReportDesigner.config file, looking something like this:

XML
<Configuration> 
    <Add Key="SecureConnectionLevel" Value="0" /> 
    <Add Key="InstanceName" Value="Microsoft.ReportingServices.PreviewServer" /> 
    <Add Key="SessionCookies" Value="true" /> <Add Key="SessionTimeoutMinutes" Value="3" /> 
    <Add Key="PolicyLevel" Value="rspreviewpolicy.config" /> 
    <Add Key="CacheDataForPreview" Value="true" /> 
    <Extensions> 
        <Render> 
            <Extension Name="XML" Type="Microsoft.ReportingServices.Rendering.
                                        XmlDataRenderer.XmlDataReport,
                                        Microsoft.ReportingServices.XmlRendering" /> 
            <Extension Name="CSV" Type="Microsoft.ReportingServices.Rendering.
                                        CsvRenderer.CsvReport,Microsoft.
                                        ReportingServices.CsvRendering" /> 
            <Extension Name="IMAGE" Type="Microsoft.ReportingServices.Rendering.
                                          ImageRenderer.ImageReport,
                                          Microsoft.ReportingServices.ImageRendering" /> 
            <Extension Name="RGDI" Type="Microsoft.ReportingServices.Rendering.
                                         ImageRenderer.RemoteGdiReport,
                                         Microsoft.ReportingServices.ImageRendering" 
                                         Visible="false" /> 
            <Extension Name="PDF" Type="Microsoft.ReportingServices.Rendering.
                                        ImageRenderer.PdfReport,Microsoft.ReportingServices.
                                        ImageRendering" /> 
            <Extension Name="HTML4.0" Type="Microsoft.ReportingServices.Rendering.
                                            HtmlRenderer.Html40RenderingExtension,
                                            Microsoft.ReportingServices.HtmlRendering" 
                                            Visible="false" /> 
            <Extension Name="HTML3.2" Type="Microsoft.ReportingServices.Rendering.
                                            HtmlRenderer.Html32RenderingExtension,
                                            Microsoft.ReportingServices.HtmlRendering" 
                                            Visible="false" /> 
            <Extension Name="MHTML" Type="Microsoft.ReportingServices.Rendering.
                                          HtmlRenderer.MHtmlRenderingExtension,Microsoft.
                                          ReportingServices.HtmlRendering" /> 
            <Extension Name="EXCEL" Type="Microsoft.ReportingServices.Rendering.
                                          ExcelRenderer.ExcelRenderer,
                                          Microsoft.ReportingServices.ExcelRendering" /> 
        </Render> 
        <Data> 
            <Extension Name="SQL" Type="Microsoft.ReportingServices.DataExtensions.
                                        SqlConnectionWrapper,
                                        Microsoft.ReportingServices.DataExtensions" /> 
            <Extension Name="OLEDB" Type="Microsoft.ReportingServices.
                                          DataExtensions.OleDbConnectionWrapper,
                                          Microsoft.ReportingServices.DataExtensions"/> 
            <Extension Name="OLEDB-MD" Type="Microsoft.ReportingServices.DataExtensions.
                                             AdoMdConnection,Microsoft.
                                             ReportingServices.DataExtensions"/> 
            <Extension Name="ORACLE" Type="Microsoft.ReportingServices.
                                           DataExtensions.OracleClientConnectionWrapper,
                                           Microsoft.ReportingServices.DataExtensions"/> 
            <Extension Name="ODBC" Type="Microsoft.ReportingServices.DataExtensions.
                                         OdbcConnectionWrapper,Microsoft.ReportingServices.
                                         DataExtensions"/> 
            <Extension Name="XML" Type="Microsoft.ReportingServices.DataExtensions.
                       XmlDPConnection,Microsoft.ReportingServices.DataExtensions"/> 
            <Extension Name="RS" Type="Microsoft.ReportingServices.DataExtensions.
                       RSDPConnection,Microsoft.ReportingServices.DataExtensions"/> 
            <Extension Name="CUSTOM_DATASOURCE" 
                       Type="DataSourceExtension.DataSetConnection, DataSourceExtension" />
        </Data> 
        <Designer> 
            <Extension Name="SQL" Type="Microsoft.ReportingServices.QueryDesigners.
                       VDTQueryDesigner,Microsoft.ReportingServices.QueryDesigners"/> 
            <Extension Name="OLEDB" Type="Microsoft.ReportingServices.QueryDesigners.
                       VDTQueryDesigner,Microsoft.ReportingServices.QueryDesigners"/> 
            <Extension Name="OLEDB-MD" Type="Microsoft.ReportingServices.QueryDesigners.
                       ASQueryDesigner,Microsoft.ReportingServices.QueryDesigners"/> 
            <Extension Name="ORACLE" Type="Microsoft.ReportingServices.QueryDesigners.
                       VDTQueryDesigner,Microsoft.ReportingServices.QueryDesigners"/> 
            <Extension Name="ODBC" Type="Microsoft.ReportingServices.QueryDesigners.
                       VDTQueryDesigner,Microsoft.ReportingServices.QueryDesigners"/> 
            <Extension Name="XML" Type="Microsoft.ReportingServices.QueryDesigners.
                       GenericQueryDesigner,Microsoft.ReportingServices.QueryDesigners"/> 
            <Extension Name="RS" Type="Microsoft.ReportingServices.QueryDesigners.
                       SMQLQueryDesigner,Microsoft.ReportingServices.QueryDesigners"/> 
            <Extension Name="CUSTOM_DATASOURCE" Type="Microsoft.ReportingServices.
                       QueryDesigners.GenericQueryDesigner,
                       Microsoft.ReportingServices.QueryDesigners"/>
        </Designer> 
    </Extensions> 
</Configuration>

Security configuration

The security is held in the RSPreviewPolicy.config file, which is in the same folder as the configuration file (RSReportDesigner.config). This file provides the security policy for SQL Server Reporting Services and allows access to your DLL.

XML
<CodeGroup
     class="UnionCodeGroup"
     version="1"
     PermissionSetName="FullTrust"
     Name="Customer_DataSource"
         Description="Code group for my Custom DataSource for data processing extension">
         <IMembershipCondition
         class="UrlMembershipCondition"
         version="1"
         Url="C:\Program Files\Microsoft Visual Studio 8\Common7\
              IDE\PrivateAssemblies\DataSourceExtension.dll"
         />
</CodeGroup>

Server Installation

To configure the Custom Data Extension on the server where your reports will be running, is a case of following the above, except you'll find the configuration files held in a different location, and depending on the installation, they appear in different places. On my installation file, they are all held in C:\Program Files\Microsoft SQL Server\MSSQL.2\Reporting Services\ReportServer\bin and the configuration files are held in C:\Program Files\Microsoft SQL Server\MSSQL.2\Reporting Services\ReportServer, with the names of the configuration files being:

  • rsreportserver.config
  • rssrvpolicy.config

As this is a server install, you will not need to make or add the <Designer> tag.

Testing of the Custom Data Source Extension

In order to test the Custom Data Source Extension, you will need to generate either a report or a Shared Data Source that points to the new custom Data Source Extension.

Create a new project within Visual Studio 2005, select a "Business Intelligence Project", and select the "Report Server Project Wizard" from the list of templates. On the new data type list, you should see "Custom DataSource Extension". If, however, you don't, but you do see CUSTOM_DATASOURCE, this means that the configuration has not picked up your DataSourceExtension.dll, and you will need to check over the configuration files.

Custom Data Source Extension

Enter a connection string, like the following: "FileName=C:\bottles.xml", you'll find this file at the top in the download section. Click next, this will take you to the "Design a Query" screen. Enter the following query: "select * from company", as shown below:

query builder

Click Next, and then continue until you have finished creating a report. You should now be able to run the report with the new Custom DataSource Extension that you can customise and shape to your own needs.

Points of Interest

Now that you have it running and working, it is worth going over how to debug the Custom Data Extension. I was not intending to cover this in this article, and will reference you to Microsoft TechNet on Debugging Data Processing Extension Code, as this should provide you with sufficient information on how to setup debugging.

History

First release.

License

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


Written By
United Kingdom United Kingdom
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
QuestionIssue in Custom DataSource Extension Pin
Member 116899853-Nov-16 22:44
Member 116899853-Nov-16 22:44 
AnswerRe: Issue in Custom DataSource Extension Pin
chakkka22-Jun-19 17:31
chakkka22-Jun-19 17:31 
To solve this, just close all instances of visual studio, also for the changes to take effect on the SSRS server you might need to restart the server
QuestionI am getting some error as explained in description, do you have any suggestions? Pin
SRS(The Coder)17-Feb-16 22:16
professionalSRS(The Coder)17-Feb-16 22:16 
QuestionHow to read a text file in SSRS using Custom data processing extension Pin
Member 112039513-Nov-14 15:33
Member 112039513-Nov-14 15:33 
QuestionUnable to preview the reports Pin
Member 989950621-May-13 3:41
Member 989950621-May-13 3:41 
AnswerRe: Unable to preview the reports Pin
Member 112035493-Nov-14 10:26
Member 112035493-Nov-14 10:26 
GeneralRe: Unable to preview the reports Pin
Member 112035493-Nov-14 10:44
Member 112035493-Nov-14 10:44 
QuestionUnable to locate the config files Pin
Member 989950621-May-13 1:48
Member 989950621-May-13 1:48 
QuestionData Processing Extension Pin
anismanoula4-Sep-12 1:40
anismanoula4-Sep-12 1:40 
QuestionCan we use custom data processing extension for report model in ssrs2008R2 Pin
ragachandrika27-Dec-11 0:54
ragachandrika27-Dec-11 0:54 
GeneralImplementing a Data Processing Extension for Report Model Pin
Sowjanya Gullapalli6-Jun-11 2:25
Sowjanya Gullapalli6-Jun-11 2:25 
GeneralImplementing a Data Processing Extension Pin
Sowjanya Gullapalli2-Jun-11 0:49
Sowjanya Gullapalli2-Jun-11 0:49 
GeneralEdit button - Select the Data Source Pin
Jay_Cee23-Feb-11 18:03
Jay_Cee23-Feb-11 18:03 
GeneralConfiguring CDE for VS 2008 Pin
somagani18-Nov-09 6:03
somagani18-Nov-09 6:03 
Questiontrainee Pin
vinthashan24-Aug-08 23:24
vinthashan24-Aug-08 23:24 

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.