Current File Name: Extract.xls
Historical File Name: Extract_YYYYMMDD_MMSS.xls
I figured this would be easy. The first thing I did was look for the OverwriteFile or ReplaceFileproperty. What I found was that the only non-connection specific property the Excel Connection Manager exposed was FirstRowHasColumnName. At that point I figured I would experiment and find out what would happen if I tried to write to a pre-existing file. The result was that the data being written to the file was simply appended to the pre-existing data in the file. Definitely not the behavior I was looking for.
I then hit "the Google" as George W. so fondly calls it. I found solutions that proposed deleting data from worksheet(s), deleting worksheets, and creating new worksheets using the Jet 4.0 SQL syntax to "DROP" and "CREATE" tables. In Jet/Excel terms, a table is an Excel worksheet. I found that the DROP didn't actually delete the worksheet but deleted rows from the worksheet. This could have worked except that the next time the worksheet is written to, the new rows start after the last row of the previously deleted data, leaving a bunch of empty rows in the beginning of the worksheet. This approach did not cut the mustard. So ended my search for the wheel I was attempting to not reinvent.
The approach I came up with does the following.
- Creates a shell (only the headers) Excel file with the name Extract_wip.xls.
- Uses a Data Flow to populate the wip Excel file.
- Copies the wip file to an Archive folder but renames to Extract_YYYYMMDD_MMSS.xls.
- Renames the wip file to Extract.xls, overwriting the previously created Extract.xls files (if one exists).