General Error: -2147217887(80040E21)
Data for Source Column 3('Col3') is too large for the specified buffer size.
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.
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:
- Double-click TypeGuessRows.
- 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.
Steps to Reproduce Problem
- Open a new Microsoft Excel workbook and create a sheet with three columns and 11 rows and include the first row with column names.
- Insert data into the cells so that the first 8 rows have data less than 255 characters in length.
- Insert data that is greater than 255 characters in length into the 10th and 11th rows. Save this sheet as ExcelSource.xls.
- Open the SQL Server Enterprise Manager and expand the Server name. Right-click Data Transformation Services, and then click Import Data.
- For the Source, select the Excel 97-2000 provider and provide the name for the Excel sheet that you saved earlier (ExcelSource.xls).
- For the destination select Microsoft OLEDB provider for SQL, and then select the destination database.
- Click Copy Tables and Views from the source DB, and then click Next.
- 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.
- Drop the table Sheet1$ from the destination database by using the command Drop Table Sheet1$.
- Follow the resolution in the "Workaround" section and make the change to the registry key for TypeGuessRows.
- Now, repeat steps 4 through 8. This time the package execution is successful.
REFERENCESFor additional information about this behavior specific to Microsoft Excel, click the article number below to view the article in the Microsoft Knowledge Base:
Article ID: 281517 - Last Review: Feb 5, 2011 - Revision: 1