Creating Report Datasets from SQL Server Integration Services
You can create a Reporting Services report that uses data generated by running an Integration Services package. The package must satisfy minimum requirements that are described in this topic.
Note
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. For more information, see Configuring Reporting Services to Use SSIS Package Data.
The following flow of events describes data processing for a report that uses package data:
Report processing is initiated when a user opens a report or when the report server generates the report through a scheduled operation.
Report server connects to the package (.dtsx) file stored on the file system or in the msdb database. The connection string in the report must specify a fully qualified path to the package file.
Report server passes the query defined in the report to Integration Services for processing. The query is the name of the DataReader destination.
Report server retrieves and then merges the data into a report layout, and renders it as specified.
You cannot trigger report processing from an Integration Services event. The event models for the products are not integrated. Report processing must be initiated from Reporting Services.
Before you can select SSIS as a data source type, you must configure the unattended execution account and the SSIS data processing extension. For more information, see Configuring the Unattended Execution Account and Configuring Reporting Services to Use SSIS Package Data.
Designing the Package
Not all packages can be used to provide data to a report. Package requirements for report data include using a DataReader destination that provides an in-memory destination target. The following list describes additional requirements and steps for creating the package and report.
In Integration Services, create a package that has at least one data flow task that fills one or more DataReader destinations. The destination object must include the input columns that you want to use in your report.
Configure a connection to an external data source. If the package connects to a SQL Server relational data source, you can choose between Windows Authentication and SQL Server Authentication. When configuring the connection, use SQL Server Authentication with a stored password.
Run the package to verify it returns the data you expect. For more information about how to set up a data viewer that shows you the data returned by the package, see How to: Add a Data Viewer to a Data Flow.
Save the package to a folder on the file system or SQL Server. Saving the package to the SSIS Package Store is not supported.
Note the name of the DataReader destination that contains the result set you want to use in the report. You will specify this name later as the report query.
Defining the Connection and Query in a Report
After you define the package, you can create a report that uses the SSIS data processing extension. The report must include a dataset that specifies a connection to the package. The dataset query is the name of a DataReader destination. The credentials that you define in the dataset are used to run the package. The following list provides more information about the connection and query definitions that you must specify in the report dataset:
Data source type must be SSIS.
The credential type can be No credentials or Windows credentials. You cannot use database authentication. When you first create the connection in the report dataset, the credentials are automatically set to No credentials. After you publish the report, you can use Report Manager or SQL Server Management Studio to switch the credential type to Windows integrated security or a stored Windows user account. If you store the credentials, be sure to select the Use as Windows credentials when connecting to the data source option. Prompted credentials are not supported.
The connection string to the package must be a fully qualified path to the package (.dtsx) file on the file system or msdb. Use the -file or -sql arguments of the dtexec utility. You do not need to specify the utility itself. The SSIS data processing extension runs the utility as a background process. For more information about the command line arguments, see dtexec Utility. The following example illustrates the connection string syntax you can use to retrieve a package from the file system:
-f c:\datafiles\packages\reportdatapackage.dtsxThe query is the name of the DataReader destination. By default, the name is DataReaderDest. You can replace it with a different name.