Hi !

Using SQL Server 2005, SSIS, SSRS ... all on Windows 2003 Server running SQL2005.

I have a business application that writes transaction-like lines to a table, which are then meant to be picked up by a batch "reporting" application and processed. For simplicity, lets say the BA doesn't print invoices by itself, rather adds lines to a "transaction like" table which is watched by another application that handles the printout.

I want to eliminate this "watch-and-print" process, and replace it with SSRS or SSRS/SSIS processes handling the stuff.

If you think of those transactions as invoices, then obviously you don't want each invoice printed more than once, so there is a flag on each record ment to go on when the line has been processed.

a) Thinking of a solo SSRS implementation, updating a flag in the record kind of contradicts what reports are meant for, to report the data, not updating it! But can it be done? If so, then can someone point me in the right direction to solve it?

b) Using a combination of SSIS and SSRS was my first though of solving this. But there is no direct (native) way of running on demand reports from within SSIS is there ?

What I have found out so far, is that probably I need to use the Reporting Services Scripting utility (RS.EXE) to handle the on-demand reporting, providing the necessary parameters either via command line arguments, or some other means (like a parameter table or something). If you do that, then is it the correct way to go to use the "Execute Process" control flow item in SSIS to run RS.EXE ? If not, then what should I use?

Finally, using the "Execute Process" to run RS.EXE, what happens when there is an error in the report generation, rendering or delivery? Does that error show in the "Execute Process" task and will it then fail? It's obviously not enough to guarantee that each invoice isn't printed twice, I also have to guarantee each one is printed exactly once, no more, no less.

Thanks,
Palli