I have a column that contains 8000+ characters, so I've defined the column as DT_Text in my flat file connection. My issue is that I'm trying to get the field into a nvarchar(4000) AND if the column is greater than 4000 characters, I need to get the rightmost 4000 characters only and cut off the rest. When I define the column as DT_STR 4000 (or even 8000) it seems to grab the 4000 (or 8000 characters) at random from the text string.
The assumption is that since SUBSTRING can be used on TEXT data type columns in T-SQL then it can be used on TEXT data type columns in SSIS - this assumption is incorrect. I will have to stage the data in SQL and scrub the data there.