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.