Excel cells with date values not importing properly via SSIS

I have an Excel '97 spreadsheet that is generated by an external automated process that I would like to import into a SQL [2008 R2] table via SSIS. The column I am importing contains text values in some cells and date values in other cells. To connect to the Excel data, I have an "Excel Source" Dat开发者_开发知识库a Flow Source, where the OpenRowset property has been set to a specific column range: Sheet1$A1:A100. I have added a grid Data Viewer directly after the Excel Source so that I can view the "raw" results.

When the package is executed, I review the Data Viewer results and all of the cells that contained a date come through as NULLs. Oddly, the text fields and number fields come through fine and show up correctly in the Data Viewer. If I put a single quotation mark (apostrophe) in front of the date value so that it treats the date as text, it imports properly. If I right-click on one of the date fields in Excel and go to "Format Cells...", they are all displayed as "Date".

These Excel files are being generated automatically by a process that I can't control, and I can't manually edit each file to get them to import properly. Within the External Columns portion of the Excel Source, the column is shown with a DataType of "Unicode string [DT_WSTR]", so I would think it would just import it as text.

I would GREATLY appreciate any suggestions on how I might get these date values to import properly. Thank you in advance!

---

I don't know if there is a "good" way to handle this situation in SSIS, but I can think of a few more-or-less ugly ideas. In order of increasing hackiness:

1. Ask whoever provides these files to you to use a more database-friendly format (instead of one where the column's data type changes from row to row).
2. If you know ahead of time which rows will contain text and which will contain dates, you could try opening the spreadsheet multiple times, changing the ranges each time so that the data type is consistent.
3. You could manually open the spreadsheet in Excel and save it as a text file; then feed the text file to your SSIS package. All the dates will be converted to text (based on your locale).
4. You could write a Windows script to open the spreadsheet in Excel and save it as a text file, then feed the text file to your SSIS package.
5. You could write an SSIS Script Task that used the Excel automation model to open the spreadsheet and handle each cell in whatever the appropriate manner might be.
6. You could roll your own Excel connection manager that could be configured to treat all cells in a particular range as text. (Hmmm. This may be getting past hacking and into yak shaving.)

I think #1 is the best long-term solution, but it doesn't sound like it'll happen quickly. That said, saving the spreadsheet as text will at least make it something SSIS can handle easily, and that might be enough to get over the immediate hurdle.