By: Siddharth Mehta | Comments (9) | Related: > Reporting Services Data Sources
Problem
SSIS packages can be used as a data-source for SSRS and this was well supported in SSRS 2005. In the SQL 2008 R2 release (Nov CTP), this data source extension is not enabled and supported. So when you create a data source in SSRS 2008 R2 (Nov CTP), you won't be able to get SSIS listed as a data source type. Therefore applications that are already using it as a data source or applications that require it as a data source get stuck. Let's learn how to enable and get SSIS listed back as a data source in SSRS 2008 R2.
Solution
Reports can be designed using Business Intelligence Development Studio (BIDS) and Report Builder. So we need SSIS listed as a data source into these applications, and secondly we need the SSIS data rendering extension listed on the Report Server also so that the report manager knows where to find this extension for processing the queries for the dataset.
First let's try to list "SSIS" as the data source at the application level, and let's focus on BIDS for the same.
"RSReportDesigner.config" is the file which we need to modify to get SSIS listed as the data source in BIDS. Search for this file and in case of a default installation, you should be able to locate this file at this location: "C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies".
Open this file using any text editor, I prefer using Visual Studio for the same as it displays XML in a well formatted manner. You will find three different sections: Render, Data and Designer. For our purpose we do not need our SSIS Extension listed in the Render section as it's a data source type and not a display format. Data section would be used by BIDS during the report preview when the report gets executed and Designer section contains the list that remains available during design time. So we would add the definition of SSIS as the data source to both of these sections.
Add the below line of code to the Data Section.
Add the below line of code to the the Designer Section.
The above hints are available on Microsoft Books Online and I have tried it out as per that guideline. After adding these hints, this file on my machine looks like the below screenshot which should be similar or exactly the same on your machine too.
Now it's time to modify the config file for ReportServer which is RSReportServer.config.
Search for this file, and in case of a default installation, you should be able to locate this file at this location: "C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies". Open this file using any text editor, and you would find many sections. Again we need to add a hint to the Data section as it's a data rendering extension. Add the below line as shown in the below screenshot. Changing the values of the rsreportserver.config file while the Reporting Services service is ON can cause an exception and the service would get stopped. Go to the configuration manager and then start the service again.
Now it's time to test the results of the above two modifications. Open BIDS, create a new report project and add a new report to the project. When you try to create a new datasource, you should be able to find SSIS in the DataSource Type list as shown in the below screenshot.
Finally we need to test whether Report Manager also reflects the change we made. Navigate to Report Manager and click on "New Data Source" on the home page. In the type list, you should be able to find "SSIS" as the data source type as shown in the below screenshot. In case you are not able to find the same, completely stop and restart the service. This works in most of the cases, but if that also doesn't help, restart your machine once.
Next Steps
- Modify the files as explained above to enable SSIS as the data source for SSRS datasets.
- Create a SSIS package and use that package as a source and test it in BIDS.
- After successful testing in BIDS, deploy the report and test the execution from Report Manager
- Read these other tips related to SQL 2008 R2
About the author
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
