Article ID: 281517 - View products that this article applies to.
This article was previously published under Q281517
When you import data from a Jet OLEDB 4.0 data source by using Data Transformation Services (DTS) and the Microsoft OLE DB provider for Jet, the data pump task may fail with this error message:
If the source has fields with more than 255 characters, the destination data for these fields may be truncated to 255 characters. DTS fails on the first row that has a field with more than 255 characters. To verify this, look up the row number that displays in the error message.
"Error at source for row number 9.Errors encountered so far in this task :1"
General Error: -2147217887(80040E21)
Data for Source Column 3('Col3') is too large for the specified buffer size.
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.
Important This section, method, or task contains steps that tell you how to modify the registry. However, serious problems might occur if you modify the registry incorrectly. Therefore, make sure that you follow these steps carefully. For added protection, back up the registry before you modify it. Then, you can restore the registry if a problem occurs. For more information about how to back up and restore the registry, click the following article number to view the article in the Microsoft Knowledge Base:
(https://support.microsoft.com/kb/322756/ )How to back up and restore the registry in Windows
To change the value of TypeGuessRows, use these steps:
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
REFERENCESFor additional information about this behavior specific to Microsoft Excel, click the article number below to view the article in the Microsoft Knowledge Base:
(https://support.microsoft.com/kb/189897/EN-US/ )XL97: Data Truncated to 255 Characters with Excel ODBC Driver
Article ID: 281517 - Last Review: February 5, 2011 - Revision: 4.0