Estou trabalhando em um script para portar um ambiente de um servidor para outro. Estou enfrentando um problema de chamada catalog.create_environment_variableem que recebo o erro "O tipo de dados do valor de entrada não é compatível com o tipo de dados de 'String'." saindo do proc "check_data_type_value."
O que é estranho é que, se eu deixar o script da GUI sair das variáveis, essa consulta funcionará
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
No entanto, essa abordagem de script não está funcionando. O trabalho braçal que fiz indica que essa mensagem de erro geralmente é resolvida usando o tipo de dados nvarchar em vez de varchar. No entanto, esse não é o caso das minhas coisas.
Linha 108 para o seguinte script. Minha suposição é que é algo instável com o sql_variant , mas não tenho ideia do que seja.
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_environment
@folder_name = @folder_name
, @environment_name = @environment_name
, @environment_description = @environment_description;
END
DECLARE
@EnvironmentVariables TABLE
(
folder_name nvarchar(128)
, environment_name nvarchar(128)
, variable_name nvarchar(128)
, description nvarchar(1024)
, data_type nvarchar(128)
, sensitive bit
, value sql_variant
);
INSERT INTO
@EnvironmentVariables
SELECT
E.folder_name
, E.environment_name
, S.name
, S.description
, S.type
, S.sensitive
, S.value
FROM
(
SELECT 'FolderBase','Root for ssis processing','String',CAST(0 AS bit),'\\myserver\ssisdata'
UNION ALL SELECT 'AuditConnectionString','Conn to audit db','String',CAST(0 AS bit),'Data Source=SQLETL01;Initial Catalog=Audit;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;'
) AS S (name, description, type, sensitive, value)
CROSS APPLY
(
SELECT
E.name AS environment_name
, F.name AS folder_name
FROM
catalog.folders AS F
INNER JOIN
catalog.environments AS E
ON E.folder_id = F.folder_id
WHERE
F.name = @folder_name
AND E.name = @environment_name
) E;
DECLARE Csr CURSOR FORWARD_ONLY STATIC FOR
SELECT
EV.variable_name
, EV.description
, EV.data_type
, EV.sensitive
, EV.value
FROM
@Environmentvariables AS EV;
OPEN Csr;
FETCH NEXT FROM Csr INTO
@variable_name
, @description
, @data_type
, @sensitive
, @value;
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'.
EXECUTE catalog.create_environment_variable
@variable_name = @variable_name
, @sensitive = @sensitive
, @description = @description
, @environment_name = @environment_name
, @folder_name = @folder_name
, @value = @value
, @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
, @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;
"O trabalho braçal que fiz indica que essa mensagem de erro geralmente é resolvida usando o tipo de dados nvarchar em vez de varchar. No entanto, esse não é o caso das minhas coisas." Ou é o caso?
Fiz duas alterações no meu cursor. O primeiro está no meu bloco CATCH. Reli o artigo sobre o tipo de dados sql_variant e segui com o sql_variant_property . Eu adicionei uma chamada para isso no meu bloco catch, esperando ver,
nvarcharmas eis que ele reportavarcharcomo meu BaseType.Sabendo disso e de que todos os meus dados de origem são baseados em caracteres, trapaceei e adicionei a
@localvariável com uma conversão explícita para nvarchar e funcionou magicamente.Análise de causa raiz
Quando comecei a escrever um resumo das descobertas, descobri a desconexão. Como eu estava carregando minha tabela temporária, @EnvironmentVariables, eu tinha originado isso diretamente de
catalog.environment_variables.Para torná-la mais portátil, copiei os valores como instruções SELECT. Aqui é onde eu estraguei tudo. Quando reconstituí esses valores, transformei as strings Unicode em strings mercantis. Eles foram gravados na coluna do tipo sql_variant como não-unicode, que explodiu quando foi passado no proc para validação. Se eu introduzir corretamente minhas strings com oNmodificador (?) Elas serão armazenadas como nvarchar.Apenas para adicionar a excelente resposta do @billinkc (com certeza você sabia que seria você quem responderia a esta pergunta !!) e enriquecer o conhecimento em torno disso ....
Aqui está um código de exemplo, gerado automaticamente a partir daqui https://thefirstsql.com/2013/05/28/ssis-2012-easily-copy-environment-variables-to-new-servers-or-new-environments/ que gera o erro :
Especificamente o erro é
Eu também tive outros erros para datetime e boolean
Portanto, sem se esforçar muito para entender o problema, a solução foi basicamente usar um tipo de dados específico, em vez de
sql_variantpassar um valor para o@valueparâmetro.Para Int64 e Boolean, você pode apenas codificar o valor, mas para
datetimevocê tem que pré-declarar uma variável do tipodatetimee usá-la - você não pode simplesmente passar uma string de data literalEsta é a primeira vez que vejo um parâmetro em um procedimento armazenado aparentemente aceitar uma variedade de tipos de dados.