Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Enable SSIS as data source type on SQL Server Reporting Services


By:   |   Read Comments (9)   |   Related Tips: > 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.

<Extension Name="SSIS" Type="Microsoft.SqlServer.Dts.DtsClient.DtsConnection, Microsoft.SqlServer.Dts.DtsClient, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"/>

Add the below line of code to the the Designer Section.

<Extension Name="SSIS" Type="Microsoft.ReportingServices.QueryDesigners.GenericQueryDesigner, Microsoft.ReportingServices.QueryDesigners"/>

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.

how to enable and get SSIS listed back as a data source in SSRS 2008 R2.

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.

<Extension Name="SSIS" Type="Microsoft.SqlServer.Dts.DtsClient.DtsConnection,Microsoft.SqlServer.Dts.DtsClient, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"/>

Now it's time to modify the config file for ReportServer which is RSReportServer.config

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.

create a new datasource, you should be able to find SSIS in the DataSource Type list as shown

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.

Navigate to Report Manager and click on "New Data Source" on the home page
Next Steps


Last Update:






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

View all my tips





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Wednesday, June 14, 2017 - 12:35:45 PM - Ashley Back To Top

How can I enable SSIS as data source on SQL Server 2016? I don't see the Designer section.  I've a 2012 server and don't see it either.  Thanks!


Tuesday, December 22, 2015 - 11:00:00 AM - Agne Back To Top

SSIS does not appear as Data Source Type in Report Manager. Everything else worked just fine.

Is there a fix for Report Manager? Or is it possible to work without it?

 

Thank you!

 


Thursday, May 28, 2015 - 11:45:33 AM - Uman Back To Top

Hi Siddharth - If I'm using SQL Server 2014, VS 2013, to enable SSIS package as data source for Report Wizard, do I need to make the above same changes.

Please clarify.

Regards!


Thursday, June 12, 2014 - 5:08:40 AM - Jean-Edouard Couderc Back To Top

Hi,

Many thanks for this contribution.

Do you think it is possible to enable SSIS  as Data source (as a connection manager ?) in SSAS Project or in another SSIS Project ?

thanks for your help.


Monday, June 09, 2014 - 7:08:05 AM - Hussein Sileem Back To Top

Also the same for me, Could you please help ?

In case anybody still reading here. Everything worked perfectly except for Report Manager, I retarted all the SQL related services and rebooted the machine, yet SSIS does not appear as Data Source Type. Any idea of what I'm doing wrong?

I'm using SSRS 2012

Thanks a lot in advance


Monday, August 12, 2013 - 5:09:42 AM - Didac Back To Top

In case anybody still reading here. Everything worked perfectly except for Report Manager, I retarted all the SQL related services and rebooted the machine, yet SSIS does not appear as Data Source Type. Any idea of what I'm doing wrong?

Thanks a lot in advance,


Wednesday, September 08, 2010 - 9:14:59 AM - Gennadiy Chornenkyy Back To Top
That's kind of undocumented functionality - I'd not use it in production. Another problem (I used in 2005 only) - long response time for the first "morning" call - you'll need to "warm up" SSIS data source or find the way to catch time out and re-run report


Wednesday, September 08, 2010 - 8:31:02 AM - jonmcrawford Back To Top
I'm unclear, is this option supposed to be supported, and it needs fixed in 2008 R2, or is this something that is no longer supported by choice?


Wednesday, May 05, 2010 - 4:47:34 PM - Gennadiy Chornenkyy Back To Top

Actually it was not enabled by default in SQL 2005 as well - to use this feature some option have to uncommented in SSRS config files (at least for RTM, SP1, and SP2)


Learn more about SQL Server tools