Saturday, June 26, 2010
I'm reading: SSIS and SSRS Integration for generating reports and sourcing dataTweet this !
SSIS and SSRS has got one of the best integration and this is also one of the factors that fills the gap or limitations of each other. SSRS is implemented as a webservice and has two endpoints. The limitation of SSIS is that there is no built-in way to generate reports, and technically it's an ETL tool, so report generation is not a normal / regular requirement too.
But in cases where report generation is required from SSIS, SSRS can be very well used for that. Read this Whitepaper on MSDN authored by me, which shows how to generate reports in bulk using SSIS and SSRS 2008 R2. In the solution that I have explained, I have used a parameterized SSRS report as a model to generate reports in different formats. For more, please read the article. Any programming language capable of making call to webservices can take the advantage SSRS Webservice.
The other way integration, i.e. from SSRS to SSIS is also very critical. There might be cases when one would need data from multiple data sources with a lot of transformation and every time you might not have a staging database where you would massage this data. SSIS Package can be used as a data source from SSRS using the SSIS Data Processing Extension.
Not only in the case of multiple data sources, in cases where you have the same kind of data source but you need to fetch data from more than one database server or more than one database on the same server, even in that case, using SSIS Package as a data source is one of the option apart from creating some routine in the source database for cross database or cross server data retrieval. My article on the same topic can be read from here.
BTW, In case if you have any feedback on my MSDN Whitepaper, please feel free to email me your feedback for the same.