July 26, 2012 at 2:22 pm
I have an SSIS package that loops through some data that I need to email it out to some 60 individuals based on their departments. I have no problem with the SSIS package, the problem is adding the data to Excel and making Excel do what I want (two days in the toilet so far).
Then I got to thinking I could use SSRS since it's so much better at reports 🙂 but I don't know if I can use my SSIS package to loop through these 60 people and send out individual SSRS reports, or if SSRS has a way to do this. Anyone have any suggestions? TIA!!
August 6, 2012 at 9:27 am
Tillman Eitelberg has created a useful set of SSIS components that includes a report generator task. I recently used it to do something similar.
http://www.ssis-components.net/page/Komponenten.aspx
I created a resultset variable from an Execute SQL task and then iterated through it using a ForEach container. This article will help:
http://www.select-sql.com/mssql/loop-through-ado-recordset-in-ssis.html
You can then use variable mappings to set the destination path and parameters to generate the report.
I was only generating a bulk export but you can presumably use the same parameters with a Send Mail task in the loop.
Hope that's useful.
August 6, 2012 at 11:03 am
I am surprised you can't get what you want in SSIS. However, SSRS also has what you need. If you have the enterprise version you can use a data driven subscription to send an email with a report customized for the recipient. You could use the execute sql task at the end of your package to kick off the report using msdb.dbo.sp_start_job
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply