SSIS variable 4000 character limit and spliting it

Question

can someone explain to me why the variable expression in SSIS 2012 is at 4000 character limit? What would happen if it does go over that amount?

I understand that there are other ways to do this like using a SP. Since it is at 4000 char limit, how can you break it up into two variables? Like put one half in one variable and the other half in the other. What are some best practices to do this?

Answer 1

Yes. It is a limitation. create the separate variables storing sections of your query for the Data Flow Task, and then use the script task to combine them all into a final variable at runtime. Refer this. http://www.texastoo.com/post/2009/07/27/SSIS-workaround-for-the-4000-byte-character-limit.aspx