UPDATE: 2012-02-24 – The bug I submitted on this has been closed as “won’t fix.” I had asked the team to enforce this limitation in the GUI instead of throwing a somewhat cryptic error. Sorry.
When creating environments in the new SSIS catalog, you are able to define environment variables. When creating string variables, you are limited to a string length of 4,000 characters. If you exceed 4,000 characters, you will receive an error. Details below.
Environment variables can be created by right-clicking on the environment in question and selecting “Properties.”
In the properties window, select “Variables” so that you can add new variables.
As long as you stay within the 4,000 character limit, you’ll be fine. If you exceed that limit, expect an error message as below:
Clicking on the error message in the highlighted progress window, you can see the full details:
Yep, internally, the SSIS team uses the sql_variant data type, which cannot store an NVARCHAR(MAX) column. Since our string is longer than 4,000 characters (the max limit for NVARCHAR), the data type of the data we are sending must be converted to an NVARCHAR(MAX) which is an incompatible type for sql_variant.
The SSIS team is aware of this and hopefully they’ll be putting in a limit to prevent users from entering anything more than 4,000 characters to prevent the error from happening in the first place, or at least raising awareness of the limitation.
Also of a somewhat unrelated note – if you choose to set a variable to “sensitive” once you save it to the database by clicking the OK button, you will not be able to retrieve its value from within the GUI. Just beware. Treat the sensitive property as if you were working with passwords (which may be a common use of this property anyhow).