"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)
Best Answer
Since Environment Variables can be used at either the Project or Package level, this query will indicate said level the variable is used and quickly return the results I want. Hopefully this will come in handy for someone else in the future.