rs80 (4/2/2013)
Phil: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).
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.