SSIS 2012 Create environment variable fails

"The legwork I've done indicates this error message is usually resolved by using nvarchar data type instead of varchar. However, that's not the case for my stuff." Or is it the case?

I made two changes to my cursor. The first is in my CATCH block. I re-read the article on the sql_variant data type and followed with the sql_variant_property. I added a call to that in my catch block, expecting to see nvarchar but lo and behold, it reports back varchar as my BaseType.

Knowing that and that all of my source data is character based, I cheated and added the @local variable with an explicit cast to nvarchar and it magically works.

WHILE @@FETCH_STATUS = 0
BEGIN

    BEGIN TRY
        -- THERE BE MONSTERS AHEAD
        -- The data type of the input value is not compatible with the data type of the 'String'. 
        DECLARE
            @local nvarchar(4000) = CONVERT(nvarchar(4000), @value);
        EXECUTE catalog.create_environment_variable
            @variable_name = @variable_name
        ,   @sensitive = @sensitive
        ,   @description = @description
        ,   @environment_name = @environment_name
        ,   @folder_name = @folder_name
        ,   @value = @local
        ,   @data_type = @data_type
    END TRY
    BEGIN CATCH
        SELECT 
            @folder_name        AS folder_name
        ,   @environment_name   AS environment_name
        ,   @variable_name      AS variable_name
        ,   @data_type          AS data_type
        ,   @sensitive          AS sensitive
        ,   @value              AS value
        ,   SQL_VARIANT_PROPERTY(@value, 'BaseType') As BaseType
        ,   @description        AS description
        ,   ERROR_NUMBER()AS error_number --returns the number of the error.
        ,   ERROR_SEVERITY() AS error_severity --returns the severity.
        ,   ERROR_STATE()AS error_state  --returns the error state number.
        ,   ERROR_PROCEDURE() AS error_procedure --returns the name of the stored procedure or trigger where the error occurred.
        ,   ERROR_LINE() AS error_line --returns the line number inside the routine that caused the error.
        ,   ERROR_MESSAGE() AS error_message; --returns the complete text of the error message. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times.

    END CATCH  

    FETCH NEXT FROM Csr INTO
        @variable_name
    ,   @description
    ,   @data_type
    ,   @sensitive
    ,   @value;
END

CLOSE Csr;
DEALLOCATE Csr;

Root cause analysis

As I started to write a summary of findings, I discovered the disconnect. As I was loading my temporary table, @EnvironmentVariables, I had originally sourced that directly from catalog.environment_variables. To make it more portable, I copied the values out as SELECT statements. This is where I screwed up. When I reconstituted those values, I made the Unicode strings into 'mercan strings. They were written into the sql_variant type column as non-unicode which then blew up when it was passed in the proc for validation. If I correctly preface my strings with the N modifier(?) they are stored as nvarchar.

FROM
(
    SELECT 'FolderBase','Root for ssis processing','String',CAST(0 AS bit),N'\\myserver\ssisdata'
    UNION ALL SELECT 'AuditConnectionString','Conn to audit db','String',CAST(0 AS bit),N'Data Source=SQLETL01;Initial Catalog=Audit;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;'
) AS S (name, description, type, sensitive, value)