question

My SSIS package needs to convert data from a varchar(max) field to DT_TEXT for processing (merge-join, conditional split, and insert or update depending on the split) and then back to varchar(max) in the destination table. However, it errors and won't just do what I tell it to - while the underlying data is just characters (not some weird BLOBs or some type of binary) it will not just push it through the pipe to the other end. I am also not, that I know of, using this specific field to do matching or conditional splitting. Does anyone know any gotchas that I should be looking out for to have this flow through properly? Is there any way to do this? I am basically using a differential patter: 1. First data flow task: if source doesn't have an id, delete from destination 2. Second DFT - ignore any with date changed + ID match; if source date field + id exist, but destination doesn't, insert; if source date + id exist, but source is has a later change date than destination, update destination. Hacks like converting to DT_STR 8000 characters won't work as this will lose data. While the data is just normal ASCII characters, I can't move it for anything. I am sure I am not the only one with this pattern - how do people deal with it? I haven't seen any clearly worded explanations anywhere.