SSIS Talk

Without fully restating what’s already published, please read this MSDN Blog Post for more details on how to determine which SQL Client version is connecting to a SQL instance.

Basically,
SELECT CAST(protocol_version as BINARY(4))
FROM sys.dm_exec_connections
will tell you which version is connecting to your instance based on the TDS protocol appendix document, http://msdn.microsoft.com/en-us/library/dd339982(PROT.13).aspx.

SQL Server 2012 is almost complete, and we now know that the TDS protocol version for this will be 0×74000004.

Another option appears to be to use the client_version column in sys.dm_exec_sessions. A client_version = 4 is SQL Server 2000. 5 = SQL Server 2005. 6 = SQL Server 2008. 7 = SQL Server 2012.

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