SSIS Excel Import Columns with More or Less than 255 Characters

Question

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.

first welcome to Stackoverflow, it is good to Read the Tour Page before asking your first question, first to get the "Informed" badge which tell other users that you have read the basic rules of the website so they will helps you more, and Second to know more about this site rules like "asking good questions, accepting answers, ..." — Hadi

Hadi Hadi · Accepted Answer · 2017-12-20T20:36:09

You have to add ;IMEX=1 to your connection string, also you have to Set the TypeGuessRows=0 property in the registry. And make sure that the first row in the Excel Worksheet contains value longer than 255.

You can follow this Detailed article for more informations, also make sure to read the comments.

SSIS Excel Import Columns with More or Less than 255 Characters

1 Answers