SSIS 2012 – SSIS Environment Variables String Limit

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.”
SSIS Environment Properties

In the properties window, select “Variables” so that you can add new variables.
SSIS Environment Variable Properties Window

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:
SSIS Environment Variables String Error

Clicking on the error message in the highlighted progress window, you can see the full details:
SSIS Environment Variables String Error Detail

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).

4 thoughts on “SSIS 2012 – SSIS Environment Variables String Limit

  • Nigel

    This has been an issue for some time. MS have been aware of this and there is even a Connect issue where they have closed giving the stock “By Design” reason.

    http://connect.microsoft.com/SQLServer/feedback/details/260730/ssis-maximum-string-length-of-4000-is-too-restrictive

    I do find the “By Design” reason somewhat worrying, in that they are ignoring a request to change the design of something that has been highlighted as not fit for purpose with an answer of essentially “It’s been designed like that and we aren’t going to change it so live with it”. Two years to get around to making such a fundamental change that will add considerable flexibility to a developer’s toolset (dynamic sql construction being just one example of where this can really help) is really rather rubbish.

    For those trying to use a nvarchar(max) datatype somewhere, it is possible to use the System.Object variable data type in SSIS 2008 to get around this limitation. Not particularly obvious if you are not coming from a DotNet programming background.

    • Phil Brammer

      The 4,000 character expression limit has been removed in 2012. So you should be able to build your dynamic SQL no problem.

  • Nigel

    My apologies, I’m confusing this limitation with that of the limitation on holding nvarchar(max) parameters in strings data type variables. This is where the System.Object data type approach is required. My apologies. You are right that the expression limitation has been removed (thank goodness). Is it possible to define an environment variable as type System.Object as a possible workaround?

  • vipul sachan

    Step by step configuration on environment variable in SSIS package –

    http://vsstack.blogspot.com/p/blog-page_6.html

Comments are closed.