question

redder avatar image
redder asked

SSIS 2005 String Variable Limit

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?
sql-server-2005ssis
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
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?
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

When using "Execute SQL Task" and assigning an update statement to the expression. The update statement is using a CASE statement with 99 values which in turn gives me the message when executing the task of maximum limit of 4000 has been exceeded in SSIS 2005.
0 Likes 0 ·
Pavel Pawlowski avatar image
Pavel Pawlowski answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.