Last post Jun 11, 2013 06:00 AM by Nasser Malik
Jun 11, 2013 05:30 AM|Dinesh Balasundaram|LINK
When i use microsoft oledb driver for excel import, it truncates the data to 255 characters.
connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=" + FilePath +
Please provide some solutions
Jun 11, 2013 06:00 AM|Nasser Malik|LINK
1. You need to do some registry settings .. See following discussion
2. The other thing can be first row in the column UserEntity in the Excel file is null. In this case, SQL Server Integration Services(SSIS) will consider the column as DT-WSTR. To work around the issue, we can sort the UserEntiry column in the Excel as Z
to A. After that, the column will be considered as DT-NTEXT in the SSIS. see following
Same solution by Asheej
When you use OLEDB providers then the datatype is determined automatically by the provider based on the first 8 rows. If you have lengthy cells in the first 8 rows then data type will be set as text and otherwise it will be memo type which can hold 255 characters.
To overcome this issue Either change the registry setting as mentioned in below KB article:
http://support.microsoft.com/kb/281517 or use Microsoft.Jet.OLEDB provider to read the data.