SSIS 2012 Create environment variable fails

I'm working a script to port an Environment from one server to another. I am running into an issue calling catalog.create_environment_variable wherein I get the error "The data type of the input value is not compatible with the data type of the 'String'." coming out of the proc "check_data_type_value."

What's strange is that if I let the GUI script out the variables, that query would work

DECLARE @var sql_variant = N'\\myserver\ssisdata'
EXEC [catalog].[create_environment_variable]
    @variable_name = N'FolderBase'
,   @sensitive = False
,   @description = N''
,   @environment_name = N'Development'
,   @folder_name = N'POC'
,   @value = @var
,   @data_type = N'String'
GO

However, taking this script approach isn't working. 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.

Line 108 for the following script. My assumption is that it's something wonky with the sql_variant but I have no idea what that thing is.

```
USE SSISDB;
GO

DECLARE
@folder_id bigint
, @folder_name nvarchar(128) = N'POC'
, @environment_name nvarchar(128) = N'Development'
, @environment_description nvarchar(1024)
, @reference_id bigint
, @variable_name nvarchar(128)
, @data_type nvarchar(128)
, @sensitive bit
, @value sql_variant
, @description nvarchar(1024);

IF NOT EXISTS
(
SELECT * FROM catalog.folders AS F WHERE F.name = @folder_name
)
BEGIN
EXECUTE catalog.create_folder
@folder_name = @folder_name
, @folder_id = @folder_id OUTPUT;

PRINT CONCAT('Folder "', @folder_name, '" has been created with a folder_id of ', @folder_id)
END

IF NOT EXISTS
(
SELECT * FROM catalog.environments AS E WHERE E.name = @environment_name
AND E.folder_id = (SELECT F.folder_id FROM catalog.folders AS F WHERE F.name = @folder_name)
)
BEGIN
PRINT CONCAT('Creating environment ', @environment_name);

EXECUTE catalog.create_envir

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

StackExchange Database Administrators Q#46556, answer score: 11