I cannot use dt_Wstr because the length is expected to be more indefinite, which definitely can be 4000 characters. I think my only out with this will be to write some sql code to pivot the data rather than using the ssis pivot transformation. But it really is strange that SSIS pivot transformation doesn't support dt_ntext datatype 😕