Posts Tagged ‘SQL 2012’

SSIS Expressions 4000 Character Limit

July 1, 2012

One of the most powerful and most overlooked features of SSIS is the ability have virtually every property of any component be controled by an expression. This allows for SSIS packages to have great flexibility.

However in SSIS 2008 the output of an expression cannot exceed 4000 characters. While in most cases your expression output will not exceed the limit, if you are ever unfortunate enough to run across this limit you will soon realize that there is no easy work-around for it.

See this Microsoft connect: SSIS Maximum string length of 4000 is too restrictive

Testing this restriction is as easy as creating a expression on a variable that returns more than 4000 characters.

Example: REPLICATE(“1”,4001)


Note: The above uses BIDS Helper to evaluate the expression.

If you change the expression to  REPLICATE(“1”,4000) then the expression is evaluated successfully.


However, this limitation has been removed in SSIS 2012. Just open up SSDT (no more BIDS) and create the following  expression on a variable:  REPLICATE(“1”,40001)