Hi all, I am trying to write a data import to SQL. I am provided data in xml format, so I logically chose to use XML source. My issue is that I cannot have strings longer than 4000 chars with the xml source, so data is being truncated. Is there a way around this (by setting the field to a different type), or am I going to have to use a script task to do what I need? I'm curious as to why there is such a limit.Andrew Berry - Software analyst/Developer

This is I guess because you used DT_WSTR - it is limited to 4000 chars, anyways, to resolve use nvarchar(max).Arthur My Blog

I created a package with an xsd that set the maximum length of an element to 20000 characters. I was presented with the warning that the limit was 4000 characters and my column was limited to 4000 characters. My understanding of that (please correct me if I am wrong) is that the interface that pulls the xml data will only pull 4000 characters.Russel Loski, MCT

The only possibility might be to use DT_NTEXT - but even if that's possible, that limits some other things you can do with the data, because DT_NTEXT is considered a CLOB type. The reason for the limit isn't completely apparent. There are lots of places in SSIS where it doesn't make any sense - expressions, for example. But in the case of Data Flow columns, it is apparent. All data types used in the Data Flow need an upper limit - a reasonable upper limit. That's because SSIS allocates a "square" buffer in memory - all rows get the same number of bytes. This GREATLY helps performance because offsets can reliably be used to seek to rows. Of course, this means that if you define a column as a DT_WSTR, 4000 but only ever use 10 characters, you're wasting an awful lot of space. It's like using a CHAR type in SQL Server where you should be using a VARCHAR. In the SSIS data flow, there isn't a VARCHAR equivalent... although DT_NTEXT comes closest. The data in DT_TEXT and DT_NTEXT columns isn't stored in the "square" buffer - it's stored in a separate CLOB area, much like VARCHAR can be in SQL Server itself. You may have to resort to a Script, and that may be the better thing for you to do anyway, due to other "one size doesn't fit all" issues with the XML source. Talk to me now on

In addition to what Todd has said you may want to read this post: http://blogs.msdn.com/b/rdoherty/archive/2005/03/16/396956.aspx, especially its bottom part.Arthur My Blog