Something you would have thought would be changed by 2015 but then we are talking Microsoft and their "do something and leave everyone to figure out workarounds for what we should have done in the first place"-ethos

Right, so I'm trying to import an Excel file with some columns > 255 characters. I have seen many posts on this but can't find the wheat from the chaff. By default, we have our driver set with "IMEX=1" which is one of the workarounds and I noted that my source Excel component was registering the "External Columns" as DT_NTEXT which is meant to be what setting the TypeGuessRows was to assist with resolving. I have also updated the "Output Columns" to be NTEXT and when running, these columns are marked as "<Long Text>" but after passing through a conversion, they are still truncated.

Although my fields were marked as DT_NTEXT, I gave the TypeGuessRows adjustment a go but to no avail, I also tried seeing what the import wizard in SSMS did but in its "wiseness" it marks the source columns as varchar(255) and I couldn't see a way to change that.

Does anyone know of any other option to try? I'm on the verge of saving to CSV files but that means a manual intervention required any time we get this feed so would prefer to be able to drop and run

I do not think your opening remarks are fair. Excel is not meant to store long textual data, yet it belongs to the Desktop family of applications and those who decide to use it for data inter-exchange are not doing it right.

I think the trouble began with " I have also updated the "Output Columns"" ... passing thru conversion truncated.

Not sure what was done.But

You supposed not to amend anything on the Excel end.

DT_NTEXT is the way to go then in the Derived Column Transformation have it (DT_TEXT,1252).

The destination ideally should be VARCHAR(MAX).

PS: I suspect this forum has already answers to this nuance.

255 characters does not relate to very many words, even in standard use of Excel that is easily passed and so Microsoft rolling with the times wouldn't go amiss.

I'm not concerned about converting away from nvarchar, the data that comes in is fine, just need it to be there and not truncated. Not sure what you mean about "not to amend anything on the Excel end", I would happily not amend anything on the Excel end but those are the only workarounds mentioned anywhere... including within this forum... but those suggestions have already been followed without any luck.

I find it awkward navigating around Excel with even one cell containing a lot of text.

The most elegant trick with minimal effort I have ever seen is to not to change anything, not a single setting.

But create a so called training Excel sheet with one row and cell that contains that longer than 255 chars cell.

The use it to develop the package, easy enough I guess.

Try it. Start a new package as I suspect something went too far.