Large Varchar(MAX) to DT_TEXT and Back (SSIS 2012)

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.

more ▼

asked Mar 15 at 11:35 PM in Default

avatar image


(comments are locked)
10|1200 characters needed characters left

0 answers: sort voted first
Be the first one to answer this question
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Mar 15 at 11:35 PM

Seen: 14 times

Last Updated: Mar 15 at 11:35 PM

Copyright 2018 Redgate Software. Privacy Policy