Has anyone come across a solution for the 4000 Character Limit of a string variable or expression in SSIS 2005? I heard about creating a Script Task to overcome this limit. Does anyone have a step by step SSIS instructions on how to do this?
There is no 4000 character limit to a string variable in SSIS, neither version 2005 or 2008. There is a 4000 limit to string expressions returning type DT_WSTR but a String variable in SSIS is not typed DT_WSTR. What is your specific concern, where you are limited by an expression returning DT_WSTR?
Hi, it depends for what purposes you will the variable with longer value use. The problem with expression which result is value longer than 4000 characters can be overcome by the `Script Task`. 1. Simply create a `Script Task` 2. Put your variable into **`ReadWriteVariables`** property of the Script Task. 3. If you use other variables in the Expression, put them in to **'ReadOnlyVariables`** property 4. Make whatever calculation inside the script and finally assign the variable value inside the script using **`Dts.Variables("User::myVariable").Value =...`** and you are done. But you have take in mind the if you will use such variable in other expression, you will again meed the 4000 characters limit. So for example, if you are building a dynamic query and this query you will use in `OLE DB Source`, then there is no issue, as you can specify a query from a variable in OLE DB Source. However if you would like to use such variable in eg. Oracle Attunity connector, which doesn't have a property for setting variable with uqery, you have to assign the query using an expression and you are again facing the 4000 characters limit as the variable itself has value longer than 4000 characters. The limit is caused by the fact, that the expression uses the SSIS DataTypes, in fact for string it uses `DT_WSTR` which is limited to 4000 characters (it is equivalent of `nvarchar` SQL Data type), but variables itself use nativ CLR types (this means for string values it uses `System.String` so it's limit is 2GB (cca 1 bilion characters). So when writing a Script Task, you are writing pure CLR code and use the CLR data types instead of the SSIS data types and bypass the limits of SSIS data types.