How to import an Excel file which has columns with more than 255 characters to SQL Server using SSIS?

When the driver determines that an Excel column contains text data, the driver selects the data type based on the longest value that it samples. If the driver does not discover any values longer than 255 characters in the rows that it samples, it treats the column as a 255-character string column instead of a memo column. Therefore, values longer than 255 characters may be truncated.

To import data from a memo column without truncation, make sure that at least one of the sampled rows in the memo column contains a value longer than 255 characters. If none of the rows are the desired length thennumber of rows sampled by the driver to must be increased to include such a row.

By default the number of rows sampled is 8, however to increase the number of rows sampled by increase the value of TypeGuessRows under the HKEY_LOCAL_MACHINESOFTWAREMicrosoftJet4.0EnginesExcel registry key.


No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |