Defining Report Datasets for Package Data from SQL Server Integration Services

  • Article

In Microsoft SQL Server 2005, you can create a Reporting Services report that uses data generated by running an Integration Services (SSIS) package. The package must satisfy minimum requirements that are described in this topic.

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 an Account for Unattended Report Processing 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.

  1. 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.
  2. 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.
  3. 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.
  4. Save the package to a folder on the file system or SQL Server. Saving the package to the SSIS Package Store is not supported.
  5. 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.dtsx

  • The query is the name of the DataReader destination. By default, the name is DataReaderDest. You can replace it with a different name.

See Also

Concepts

Connecting to a Data Source
Defining Report Datasets
Creating a Report with Report Designer

Other Resources

Tutorial: Creating a Basic Report
DataReader Destination
Designing and Creating Integration Services Packages

Help and Information

Getting SQL Server 2005 Assistance