Will the length ever be greater than 4000?
If not, you can just take DT_WSTR with a length of 4000.
Not great for package performance, but using a PIVOT isn't either 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP