How to use SSIS to execute a report

[smisich]

How do I use SSIS to run a report and email me the report output as either a pdf or web page? Basically I want to replicate the subscription functionality of reporting services and schedule the report to run every day using a SQL Server Agent Job.

 

[Alan0568]

Sorry can you clarify? If you use the subscription functionality you can set up a daily schedule, in doing so its a job agent job that gets created to perform this.

Not sure what you angle is with SSIS but you could lift the t/sql from the job agent step and execute it from an execute sql task within a SSIS package.

 

[zbleslav]

Hi smisich,

You can download solution from my blog:

http://msbiblog.spaces.live.com/blog/cns!3738F9E9EADAAE6A!116.entry

There is no e-mail task, but you can use it as a good start.

 

[smisich]

Thanks for the reply. I have used a combination of the RS utility, RSS file, VBscript, IS package and some database tables to replicate the data driven subscription functionality. Note we are on the SQL 2005 Standard Edition.
We have about 25 distinct reports that get run each month for each of our 20 branches and exported as a pdf. Each report has its own unique list of parameters. Some are dependent on the branch ie they may have a different commission rate%. Some reports are run for more than one item in the parameter list of values eg Category A,B,C. I have created a package that loops through each report, gets the branch(s) that requires the report, then gets each parameter for the report and branch combination from the DB, creates an RSS and BAT file dynamically and then executes the BAT file for each branch exporting a PDF file to the YYYYMM/Branch name folder.

I will definitely try you method. Thanks