SSIS Wizard Cannot Import Text Columns Longer Then 255 Using Excel Source (2)

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.

 

Steps to Reproduce Problem

<script type=text/javascript> loadTOCNode(2, 'moreinformation'); </script>

1. Open a new Microsoft Excel workbook and create a sheet with three columns and 11 rows and include the first row with column names.
2. Insert data into the cells so that the first 8 rows have data less than 255 characters in length.
3. Insert data that is greater than 255 characters in length into the 10th and 11th rows. Save this sheet as ExcelSource.xls.
4. Open the SQL Server Enterprise Manager and expand the Server name. Right-click Data Transformation Services, and then click Import Data.
5. For the Source, select the Excel 97-2000 provider and provide the name for the Excel sheet that you saved earlier (ExcelSource.xls).
6. For the destination select Microsoft OLEDB provider for SQL, and then select the destination database.
7. Click Copy Tables and Views from the source DB, and then click Next.
8. In the Select Source Tables dialog box, select Sheet1$, click Next, and then run the package immediately. The package execution fails with the error message shown in the "Symptoms" section.
   
   
9. Drop the table Sheet1$ from the destination database by using the command Drop Table Sheet1$.
10. Follow the resolution in the "Workaround" section and make the change to the registry key for TypeGuessRows.
11. Now, repeat steps 4 through 8. This time the package execution is successful.