I’ve noticed some confusion in the community over the mythical “4000 character limit” in SQL Server Integration Services (SSIS) and thought it may be worth clarifying the situation.
The most common misconception I hear is “A SSIS string variable has a maximum length of 4000 characters”. This isn’t true and its easy to prove; simply create a string variable and paste in a value that is greater than 4000 characters (which is easily constructed in Notepad using copy/paste). You can then use a script task to display the whole value, thus proving that variable values an be greater than 4000 characters:
So then where does this notion of a 4000 character limit come from? Well, 4000 is the maximum length of the result of an expression and that’s easily proved as well by using REPLICATE(“a”, 5000) in an expression. If you use that expression to return the value of a variable then you will get, at design-time, the following message:
If you examine the message in the screenshot above you’ll notice that it mentions the maximum length of a DT_WSTR value is 4000. This is true, but DT_WSTR is not the type of a SSIS string variable and hence as I’ve just proven we can place a string greater than 4000 characters into such a variable. The SSIS variable type system is basically a subset of the .Net type system whereas expressions and dataflow columns use a type system that is proprietary to SSIS (i.e. DT_WSTR, DT_STR, DT_I4 etc…). It is of course utterly ridiculous that SSIS has two different type systems; I’m completely miffed as to why that is the case.
Hopefully that clears up the confusion! This information is true of both SSIS 2005 and SSIS 2008.