Articles > MSSQL > How to use SSIS package as a data source for SQL Server Reporting Services 2008 R2

How to use SSIS package as a data source for SQL Server Reporting Services 2008 R2

Problem
There are two constraints with using SQL Server Integration Services 2008 R2 (SSIS hereafter) packages as a data source with SQL Server Reporting Services 2008 R2 (SSRS 2008 R2 hereafter). Firstly, SSIS is not enabled as a data source type itself on any regular SSRS 2008 R2 installation and secondly specific configurations needs to be made so that SSRS reports can use data from an SSIS package as the data source which in turn would be executed by SSIS. In this tip we will discuss an example of how to deal with this topic. Please keep in view that this tip assumes some basic level of SSIS and SSRS working knowledge from the reader.

Solution
To deal with enabling of SSIS as a data source type on BIDS designer and Report Server, please read my previous tip where I have explained how to enable it.

In the discussion forward, we will take it for granted that SSIS is enabled as a data source type on your development machine where you would be testing or trying out this example. Also you will need the AdventureWorks database which we will be using in this example.

Follow the exercise below to create a report and a package which we will be using to devise the solution for the problem at hand.

1) As we will be using a SSIS package as the data source, firstly we would create a package. Create a new SSIS package, name it "SSRSSourceTest". For the sake of simplicity of understanding, we will keep the package simple as the purpose of this package is to just return some data that we can use in the report that we would be developing going forward in this exercise.

2) Add a DataFlow Task and within it add an OLE DB Source, and connect it to the "HumanResources.Department" table. Link this source to DataReader Destination and name it "MySSISDataSrc". All the columns included in the DataReader Desitnation would be available in the report dataset as we will be using it as the data source in the report.

Selection of DataReader Destination as the output receiver and name of this DataReader Destination is very important for two reasons: DataReader would be used as a source in the report and its name would be used as the source name when we would create a dataset in the report.

After this is done, your package should look something like the figure below. Now place this package at a path on your machine from where you would like to access it. For the sake of keeping the package path short, I placed it on "D:\" drive of my machine.

3) Now it's time to create our report from where we would use the SSIS package created in the steps above as the source of data. Create a new report project, add a new report and name is "SSISSourceTest". Let's get started with creating a DataSource for our report.

As you can see below there are two values that you need to configure: Data Source Type and Connection String. Select Type as "SSIS" and Connection String as mentioned in the figure below. We use the -f option to specify the path of the SSIS package file, and those who are aware of the use of DTExec SSIS utility, would be able to easily recognize this option. SSIS Data Processing Extension (SSIS DataSource Type) will be passing the same parameters to SSIS for execution of the package we specify in the connection string.

By default the Credentials selcted would be No Credentials, keep it as it is.

4) Now it's time to create the Dataset. Create a new dataset and specify the settings as shown in the figure below. Note that in the Query we specify the exact name of the DataReader Destination, as it's considered the same as specifying the name of a table in a database. After specifying these options, click on "Refresh Fields" and if everything has been done as explained in the above steps, you should be able to see all the fields available in the Fields Tab. This functionality is the same as testing connection from any DataSource dialog box.

5) Now drag a Table control on the report, add some fields from the dataset to it and then preview your report. I added three fields for the purpose of testing, and my report looks like the below figure in the Preview tab.

6) So half of the battle is already won, but there's still half left. Deploy this report on the report server, by right-clicking on the solution and selecting Deploy. Navigate to Report Manager and select the Manage option as shown in the figure below.

7) Now we need to manage the authentication settings for the dataset. The SSIS package will be executed from Reporting Services and authentication information would be passed from SSRS to SSIS for execution of the package. Only two authentication mechanisms are supported: Windows Integrated Security or Credentials stored on Report Server.

If you configure the authentication settings as shown in the figure below, the expected behavior is that SSIS would execute the package under the credentials passed from Reporting Services by using impersonation. But this doesn't happen in this version and this probably is one of the reasons that SSIS is not supported as a data source type on SSRS. Supporting evidence of this theory is that a note can be found on BOL which says:

"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."

So the solution to this issue is that the Reporting Services execution account should have at least the same level of privileges that SSIS execution account needs to execute packages, so configure your authentication accordingly.

8) After configuration, execute the report from report manager and if everything is done as per the above instructions, your report should execute smoothly from report manager as shown in the below figure.