posted on Saturday, May 20, 2006 3:46 PM
Configure Reporting Services to consume SSIS packages
A really cool feature of SQL Server 2005 is the ability to use a SQL Server Integration Services (SSIS) package as the source for a SQL Server Reporting Services (SSRS) report. There's an example in chapter 17 of the Wrox book Professional SQL Server 2005 Integration Services.
Before you can take advantage of this functionality, you have to make a couple changes to some SQL Server config files.
Note: SSRS and SSIS must be installed on the same server to utilize this functionality.
Important Note: There are security implications to consider. Please see Setting the Protection Level of Packages for more information.
Navigate to C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies (assuming you installed SQL Server on the C: drive) in Windows Explorer. Open the RSReportDesigner.config file in a text editor:
You can use any text editor - or even the Visual Studio 2005 IDE. Call me old-fashioned, I like Notepad. :)
Remove the comments from the config sections (there are two) beginning with the phrase: <Extension Name="SSIS":
Don't forget the End Comment markup ("-->") at the end of each commented line.
Save the file and close Notepad.
Next, navigate to C:\Program Files\Microsoft SQL Server\MSSQL.n\Reporting Services\ReportServer and open the rsreportserver.config file:
Note: On my demo machine, the file is located in ...\MSSQL.3\.... This will vary depending upon the number and type of SQL Server 2005 family of products you have installed on your machine.
Again, uncomment the section beginning with the phrase: <Extension Name="SSIS":
Save the file and close it.
Now, when you create an SSRS data source, you have the option of using SSIS as the data source:
How does this work? When your report refreshes, Reporting Services will call and execute the SSIS package. An important security consideration is the SSRS service account provides the security context for the SSIS package. For this reason, it is a good idea to hard-code SSIS connection string security data - you do not want to give the SSRS account the same permissions as you give an SSIS package.
Technorati Tags: SQL Server 2005 SSIS SSRS Reporting Services Integration Services Data Source