The issue is this: The way Excel is setup and interacts with SSIS, the first 8 rows of the column are critical (as set by the registry default). If you have mixed data types in the first 8 rows, ie numerics in some and alpha characters inothers, all will be imported fine. However if for example you have only numerics in the first 8 rows, then the column is "determined" to be an Integer and any alpha characters are dropped when the data is imported. The two solutions I have found so far are:

1. Make sure if there is to be alpha data in brought in from the column then some is in the first 8 rows.

2. Hack the registry on the machine where the job will be running to look further than 8 rows.

I would list the "IMEX=1" "fix" except when the data is numeric for each of the first 8 rows in the column then this is overridden. So for my purposes (over 15,000 lines of data and no alpha data until about 7,000 lines in) this is a non-solution.

I am currently exploring alternate solutions but I may have to go back to the source and require alpha data in the first 8 rows.