DT_TEXT not supported while importing from an XML file?

I have an XML file that contains a field that has over 8000 characters in it. I cannot use a (n)varchar for it, I must use a Text for it (although as a last resort I could split the string into several (n)varchar columns). I want to set the external column of the XML column to a DT_TEXT and I receive and error message that states :

Error at Import XFFD Data [xffd [1]]: The SSIS Data Flow Task data type "DT_TEXT" on the external metadata column "reviewText" (32411) is not supported for the component "xffd" (1).

I've tried converting the nvarchar into a text stream with the use of a Data Conversion Transform, but I think the Validating steps are truncating the field.

HELP. I've been beating my head against this for a couple hours a day.

Thanks,
Scott

The XmlSrc only supports Unicode text. Set the output column type of the XML source to DT_NTEXT. If you need it to be DT_TEXT downstream, you would then need to use a data conversion transform to convert to DT_TEXT.

Thanks
Mark

DT_Str and trailing whitespace?

I have a OLE DB Source going to a flat file destination. My source is a sql variable with "select * from tablename" which have varchar datatypes. Yet I'm getting trailing spaces at the end of some of my columns (for instance, my address column).

I've checked the data by doing a "select Max(Len(address)) from tablename" and the max is only like 34 chars, yet each of them have 100 chars total.

Taking a look at my flat file connection, the outputColumnWidth is 100, and datatype is string [DT_STR]. Am I crazy? What's the problem here? Is the DT_STR datatype the equivalent of char, and not varchar?

Any help, of course, will be appreciated.
LEN does not count trailing spaces.

Try this: select max(len(replace(address,' ','*')))
|||What about using a derived column in the dataflow using ltrim/rtrim/trim function?|||Are you using a fixed length flat file format, or a delimited format?|||Yeah, are you doing a fixed width format on your file? Please share the format of your destination flat file. Fixed width will be 100, in this case. No way around that.|||Thanks for the responses...

The flat file destination is delimited, not fixed width.

As I checked the select max(len(replace(),' ','*')), I see that the data is bad... thanks so much for the help!!! Think that should fix it.

|||I'm curious to know what's bad about the data. Varchars don't store trailing spaces so I'm curious to just know the end result.|||Basically I have about 4 SSIS Packages taking data through a long set of processes. In one of the initial processes, I found that the address field was being stored as a char(500). Later on in the process, it's run through a data conversion that put it as a varchar(100), which is where it remained through the rest of the packages. But at this point, I guess, the trailing spaces were already in there, and remained in there.

I had thought the data was fine this whole time, mainly because when I used the TSQL len() function, the data seemed fine. Same with in Management studio, I'd double-click the separators between column headers to let it auto-resize the column to fit the largest address, without spaces at the end.

Glad I found it... thanks for all the help. =)