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.
Article 
7 comments:
Good article and thanks for sharing.
I am finding the scheduling options in SSRS is very limited. If I have to schedule a report every hour from 9.00 AM to 5.00 PM Can't do it is SSRS but use SQL Job scheduler to fine tune it.
I am thinking of doing something like this using SSIS, whereby I can create a schedule using SSRS webservices from data stored in a table. or USE SSIS to run the report at aspecified intervals.
Any thoughts ??
Regards
Jimmy George
One option is, create a schedule in SQL Agent that runs every hour. Create a job using this schedule and execute SSIS package. This SSIS package can call SSRS webservice and generate all your reports. You can configure your SSIS package to generate reports based on your time requirements. But then you would have to deal with the report delivery.
Another option is you can add/remove or enable/disable selected subscriptions for the report using SSRS webservice from SSIS or by querying report server database using T-SQL. And this SSIS package or TSQL script can be executed at required time intervals through a SQL Server Agent job. The following links would help you in what you are looking for.
http://social.msdn.microsoft.com/Forums/en/sqlreportingservices/thread/4d14759c-b7fc-4d23-b148-f742f56b03b8
http://www.codeproject.com/KB/reporting-services/DynamicSSRSSubscriptions.aspx
hello Siddharth,
Do u have any idea about how to enable editing a column in database via SSRS. please respond earlier i m waiting for ur quick response.
Thanx
Rohit
Reports are meant to view data and not edit data in data sources. Applications are aimed for the same purpose. In my views, you cannot edit data using SSRS reports.
Good article...
HI Siddath,
I read your article "Bulk Report Generation using SSIS and SSRS 2008 R2" on MSDN white papers.And it sound very good. I struggled to do implement the same as you explained and finally couldn't solve :(
Is it possible to share the report and SSIS package what you have done in the article.It would be great if you share,please mail maruthi.vm@gmail.com. Thanks in advance :)
Regards,
Maruthi
HI Siddath,
I read your article "Bulk Report Generation using SSIS and SSRS 2008 R2" on MSDN white papers.It was very helpful and I was able to implement for many of my automated reports that ran with parameters. But now we have upgraded our systems to SQL Server 2014 and we need to do the same. Could you please let me know if you have details on how to implement bulk report generation in 2014. If so, it would be great if you could share it.
Thanks,
Vidhya
Post a Comment