SSIS Expressions 4000 Character Limit

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)

image

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.

image

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)

image

Advertisements

Tags: ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: