SSIS Cannot map the lookup column. NVARCHAR(MAX) error

Question

I am writing ETL. I have created View in my source database. My View is a join of two tables. Now, I need to fetch data from View. But there are two columns in View which have nvarchar(max) data type.

But when I perform lookup operation in DFT, I am facing this error:

Cannot map the lookup column, 'Description', because the column data type is a binary large object block (BLOB).

I have seen following links:

1. SSIS Lookup By NVARCHAR(MAX) Column
2. SSIS Lookup with Derived Columns

Note that, Description column may have large amount of text.

Image is attached for reference. Thank You!

Answer 1

What you is a lookup, and the lookup transformation supports join columns with any data type, except for DT_R4, DT_R8, DT_TEXT, DT_NTEXT, or DT_IMAGE (i.e. BLOB's)

Personally I try to avoid handling BLOB's as much as possible in SSIS. Convert and treat the BLOB as a nvarchar with a max value, and you should be fine.