rs80 (4/1/2013)
I didn't have evaluate as expression set to true for ExceptionReportName. After making that change and running the package, a package validation error comes up. "Error at Exceptions Report: Opening a rowset for "Sheet1$" failed. Check that the object exists in the database." The value of ExceptionReportName is C:\Temp\ExceptionReport_201304111551.xls. I copied the excel file to C:\Temp\ and renamed the excel file to ExceptionReport_201304111551.xls. But I still get that error.Not sure what I'm doing wrong. I also tried setting the value of ExceptionReportPath to C:\Temp
If I can't get this working soon I'll try Phil's method.
It sounds like the package has moved beyond not generating the spreadsheet to having problems populating the worksheet (Sheet1$). It may be because the file name contains a timestamp. The time stamp will change each time you run the package so copying a file with the name won't help for the next time the package evaluates--the file's name will be different. If that's what's going on, it's probably best to go with Phil's suggestion. Build and populate the excel file with a static name then do a file system operation to rename the file. The timestamp shouldn't be an issue at that point.