Expression result has a 4000 character limit
I have a SQL generation package that I am using to create sql files that will later be executed against the production environment. It is necessary to do this for corporate IT policy reasons which I won’t go into, however, suffice to say it isn’t the most efficient way to get new data into the database!
The data is input into Excel by the users and then passed to me the run the SSIS package. This worked fine until recently when one of the Excel spreadsheet I recieved had cells in it that were in excess of 8000 characters wide (containing Legal T&Cs).
Amazon Link: The Rational Guide to Extending SSIS 2005 with Script (Rational Guides)
At this point my package failed due to a limitation on the expression engine. It cannot evaluate expressions whose result is bigger than 4000 characters. So even if the expression evaluates at design time, be aware that if one of the columns in your dataflow is DT_TEXT or DT_NTEXT, and you are using the result in your expression, there is a good chance it will break this rule.
The workaround for me has been to use a script component to build the String instead of an expression. This works however is an untidy solution to the problem.
Cheers
Frank