• Article

Configuring Reporting Services to Use SSIS Package Data

Reporting Services includes a data processing extension that retrieves data from a SQL Server Integration Services (SSIS) package. The SSIS data processing extension is not supported. This data processing extension is a non-production feature that is off by default. Using the SSIS data processing extension on a production server is not recommended at this time. If you choose to enable the feature and use it on your report server, be aware that at run time the package will be processed under the security identity of the Report Server Windows service account or the Report Server Web service account. This has implications on how you secure the data sources that the package accesses. Specifically, you will need to grant the service accounts permission to read the data and perform any operation that you define in the package. Use this feature only if you take the following precautions:

  • Make sure the content of the package is controlled (for example, you can use the password protection features in SSIS or set permissions on the file). For more information, see Setting the Protection Level of Packages.

  • Design the package so that it can only perform those operations that are necessary for getting the data you want.

  • When configuring the report server service accounts, always use least-privilege user accounts or service accounts.

Before you can use the SSIS data processing extension, you must modify configuration files to enable the extension on your report server and in Report Designer. Follow the instructions in this topic to edit the configuration files.

Enabling the Integration Services Data Processing Extension for Report Designer

To enable the data processing extension, you must remove comments from two sections in the RSReportDesigner.config file.

  1. Install Integration Services on the same computer on which you installed Reporting Services.

  2. Using Windows Explorer, locate <drive>:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies.

  3. Using a text editor, open the RSReportDesigner.config file.

  4. Remove the comment marks (delete <!-- and --> characters from the beginning and end of the line) for this entry in the <Data> section:

    <!-- <Extension Name="SSIS" Type="Microsoft.SqlServer.Dts.DtsClient.DtsConnection,Microsoft.SqlServer.Dts.DtsClient, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"/> -->

  5. In the <Designer> section, remove the comments from this entry:

    <!-- <Extension Name="SSIS" Type="Microsoft.ReportingServices.QueryDesigners.GenericQueryDesigner,Microsoft.ReportingServices.QueryDesigners"/> -->

  6. Save the file.

After you modify the configuration file, the SSIS data processing extension becomes available as a data source type that you can select in the Data Source dialog box in Report Designer.

Enabling the Integration Services Data Processing Extension on the Report Server

Before you can process a report that retrieves package data, you must remove comments from RSReportServer.config file.

  1. Open the in RSReportServer.config file, located by default at <drive>:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportServer.

  2. Remove the comment marks (delete <!-- and --> characters from the beginning and end of the line) for this entry:

    <!-- <Extension Name="SSIS" Type="Microsoft.SqlServer.Dts.DtsClient.DtsConnection,Microsoft.SqlServer.Dts.DtsClient, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"/> -->

  3. Save the file.

After you edit RSReportServer.config, the SSIS data processing extension can be used on the report server. To verify that the data processing extension is available, open a data source property page in either Report Manager or Management Studio and view the list of data processing extensions in Data Source Type. If you modified the configuration file correctly, SSIS appears in the list of data source types.