How to increase the number of rows in the Excel file that SSIS validates on metadata:
The Jet OLE DB provider reads a registry key to determine how many rows are to be read to guess the type of the source column. The registry setting is:
By default, the value for this key is 8. Hence, the provider scans the first 8 rows of the source data to determine the data types for the columns. If any field looks like text and the length of data is more than 255 characters, the column is typed as a memo field. So, if there is no data with a length greater than 255 characters in the first 8 rows of the source, Jet cannot accurately determine the nature of the data type.
The valid range of values for the TypeGuessRows key is 0 to 16. However, if the value is 0, the number of source rows scanned is 16384. Keep in mind that a value of zero may cause a small performance hit if the source file is very large.
To change the value of TypeGuessRows, use these steps:
On the Start menu, click Run. In the Run dialog box, type Regedt32, and then click OK.
Open the following key in the Registry editor:
Note For 64-bit systems, the corresponding key is as follows:
In the DWORD editor dialog box, click Decimal under Base. Type a value between 0 and 16, inclusive, for Value data.
Click OK, and then exit the Registry Editor.
A second way to work around this problem (without modifying the registry) is to make sure that rows with fields, which have data 255 characters or greater, are present in the first 8 rows of the source data file.
This solution may also be used for ‘Mixed data types in Excel column’.