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 in 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
Setting IMEX=1 in the connection string still gets this error.
ssis is pretty strict. If you can detect the type you have, write two packages, one for the first case and one for the second. choose one or the other at runtime depending on the type.
Hi, Thanks for the response. Can you please let me know how to determine the type runtime?
You'll likely need a script task that opens the spreadsheet and looks at the columns