I have an excel file with columns that may have more than 255 characters but sometimes the number of characters may be less than 255. In this case changing the registry value to check the whole sheet versus the first 8 rows does not work because on
one file there may be more than 255 characters and on other files every row may have less than 255. How can this be handled with SSIS and Excel?
If you set the output of columns that could be greater than 255 to [DT_NTEXT] it works when that is the case, but if all the rows for that column are less than 255 then you get the error:
Failed to retrieve long data for column Using the ACE OLEDB 12.0 provider and setting IMEX=1 in the connection string still gets this error.
Thanks I have seen both of these sites and they do address the issue but ONLY if your Excel sheet has at least one row that exceeds 255 characters. If the column has more than 255 characters for some files and less than 255 for others then this does not