Mas que ??????? é esta? Usar variáveis e parâmetros em SSIS com menos confusão.

If you prefer reading this in English, the same content is available at my company’s blog.

Como parte do meu trabalho, uso frequentemente o SSIS, quer para desenvolver um package de raíz quer para alterar um existente.

Uma das coisas que uso e abuso são parâmetros e variáveis. Frequentemente abro uma Execute SQL Task e vejo este “lindo” código…

insert into dbo.sometable
(col1, col2, col3, col4, col5, col6, col7)
values
( ?,?,?,?,?,?,?)

Até parece que não vai haver problema nenhum… se algum parâmetro for trocado de ordem, é “simples” trocar a ordem das colunas no insert. Raramente temos de mexer na ordem dos parâmetros no mapeamento.

A questão é que, não poucas vezes, temos mais parâmetros do que este exemplo. Bastante mais… Imaginando que algumas necessitam de um cast() ou de um convert(), está instalada a confusão.

Eu uso bastante Event Handlers pela flexibilidade que têm, e muitas vezes ao criar uma mensagem de log tenho mais de 20 parâmetros que, mais tarde ou mais cedo, alguém vai pedir “uma pequena alteração”, quer seja alterar uma ordem, colocar mais um parâmetro lá pelo meio, etc.

E ao fim de algum tempo lá aparecem os erros…
[…]
SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED
[…]
Conversion failed when converting date and/or time from character string.
[…]
Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.
[…]
“No value given for one or more required parameters.”
[…]
“Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
[…]
“Value does not fall within the expected range”
[…]

Para ajudar nesta situação, declaro variáveis no meu SQL para as usar com mais controlo, com o bónus de as poder reutilizar mais abaixo (raro, mas já me aconteceu)

declare @MyVariableName0 as varchar(50) = ?
declare @MyVariableName1 as varchar(10) = ?
declare @MyVariableName2 as varchar(20) = ?
declare @MyVariableName3 as varchar(50) = ?
declare @MyVariableName4 as varchar(50) = ?

insert into dbo.sometable (somemessage)
values
(
' MyVar0 = '+ @MyVariableName0 +
' MyVar3 = '+ @MyVariableName3 +
' MyVar1 = '+ @MyVariableName1 +
' MyVar2 = '+ @MyVariableName2 +
' MyVar4 = '+ @MyVariableName4 +
' And again MyVar0 = ' + @MyVariableName0
)

Trocar a ordem de um parâmetro? Limpinho.
Adicionar um no meio da string? Ok, sem problemas.

Espero que vos dê tanto jeito como me dá a mim.