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

By:   |   Comments (10)   |   Related: > Reporting Services Data Sources


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.

we will be using a SSIS package as the data source

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.

there are two values that you need to configure: Data Source Type and Connection String

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.

in the Query we specify the exact name of the DataReader Destination

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.

drag a Table control on the report, add some fields from the dataset to it and then preview your report

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.

 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

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.

 the Reporting Services execution account should have at least the same level of privileges that SSIS execution account needs to execute packages

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.

 your report should execute smoothly from report manager as shown in the below figure.
Next Steps
  • Download and install SQL Server 2008 R2 Nov CTP along with AdventureWorks 2008 R2 database.
  • Create your SSIS package which fetches data from different data sources.
  • Try creating a report that uses this package as the data source.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Siddharth Mehta Siddharth Mehta is an Associate Manager with Accenture in the Avanade Division focusing on Business Intelligence.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Tuesday, November 22, 2016 - 2:06:08 AM - Julian Feinberg Back To Top (43822)

 

 I followed your steps but can only see SSIS in the drop down data type list in BIDS. I cannot see it when creatining new data source in report manager.

Also when I run a package in BIDS after assigning the SSIS source to it I get following error

"Unable to connect to data source 'DataSource1'.The selected data extension SSIS is not installed or cannot be loaded.  Verify that the selected data extension is installed on the client for local reports and on the report server for published reports."

I am running windows 10 home edition 64 bit, with sql server 2014 and BIDS 2012.

Please advise

 


Friday, August 15, 2014 - 11:27:18 PM - nani Back To Top (34163)

hai

what is the -f in datasource


Tuesday, August 5, 2014 - 5:25:06 AM - Ganesh Back To Top (34003)

If anybody need to work for SSIS, then install the Integration Service Project during the SQL Server Installation itself otherwise need to install separately. Anybody need more information regarding SSIS & SSRS development please touch with my personal mail([email protected]).


Monday, February 3, 2014 - 7:16:59 AM - gopinath Back To Top (29315)

as metioned above i done but im getting below error window with detail when clicking refresh fields, please do the needfull

Unable to connect to data source 'DataSource'.

details: 5


Sunday, May 19, 2013 - 4:36:59 PM - sham Back To Top (24037)

Sorry forgot to metion where got that error..

i got it after creating a dataset and clicked on fields!!


Sunday, May 19, 2013 - 4:33:32 PM - sham Back To Top (24036)

Hi,

Am New to SSRS,

Am trying to do same process as mentioned but am getting following error..

"The selected data extension SSIS is not installed or cannot be loaded.  Verify that the selected data extension is installed on the client for local reports and on the report server for published reports."

I have changed the config files "RsReportDesign" in my loacal machine and "RsReportServer" file on main server. am using visual Studio 2010.

Any Help!!


Monday, January 7, 2013 - 5:36:13 PM - CK Back To Top (21304)

In step 2, you need to edit the DataReader Destination. Click the Input Coumn tab to choose all of the coumns, in order to populate the fields in dataset in step 4.

For the RM part, it worked me after I changed both the SSIS and SSRS service accounts to a local win admin account (SQL Server 2008R2, Win 7 64-bit Laptop).


Saturday, September 1, 2012 - 5:53:58 PM - Pankaj Back To Top (19364)

HI Sid,

I have created a ssrs report using ssis as datasource which runs fine in BIDS but fails in Report manager with following error. I am using an excel sheet to get data into datareader in ssis package. I have full admin rights on server. both ssis and ssrs are on same system.

  • An error occurred during client rendering.
    • An error has occurred during report processing.
      • Query execution failed for dataset 'dataset1'.
        • The package failed to execute.

Thanks in advance
Pankaj


Tuesday, June 22, 2010 - 7:08:51 PM - siddhumehta Back To Top (5736)

Try assigning the same account to your SSRS windows service that your SSIS service is running on. The issue here is that impersonation does not work, so when SSRS data extension is trying to access SSIS package, it is not able to connect to SSIS package due to permissions. So you get a connection failed error. I have been able to deploy it and it runs successfully.

 Easiest soln is, first try assigning max. privileges to SSIS and SSRS service, and check if this works. If yes, then it's just a mere issue of permissions and if not, then there is more to investigate.


Tuesday, June 22, 2010 - 6:25:35 PM - sobenavi Back To Top (5735)

Hi, when i work in BIDS all works fine.. but when i deploy at server i get this error:

  • An error occurred during client rendering.
      • An error has occurred during report processing.
        • Cannot create a connection to data source 'SSIS_BI'.
          • The package failed to validate. 
    •  

      What im doing wrong?  what happend?...  :(..  please help me..















      get free sql tips
      agree to terms