Just to make sure I've understood your approach:
1) In the data flow, I would load the data from the oledb source to the template.
2) Then I would use a file task to copy the template to whatever file name and location I want.
In this case wouldn't the template file keep on growing everytime I run the package with records from the oledb source? Do I have to purge the records in the template somehow? For example, run 1 of the package I add 10 records from the oledb source to the template, copy the file and rename it as ExceptionReport_201304021023. Run 2 of the package I add another 10 records from the oledb source to the template. In this case the template has 20 records and the exception report also has 20 records. However, I just want the last 10 records in the exception report.
Hope my question makes sense.
You have understood my suggestion well - makes sense and the answer is yes.
Keep a blank copy of the template somewhere handy and use a FST to copy it over the top of the Excel destination file before each data flow runs, to avoid appending data.
If you do a search on these forums, you will also find a technique to remove existing rows from an Excel spreadsheet using a SQL-like DELETE command. I've never implemented this though (I always try to export to CSVs, because mixing SSIS and Excel gives me palpitations).
Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this
If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.