Parametrização de Projetos do SSIS

5 de fevereiro de 2020 Off Por Luciano Gambato

Neste post iremos abordar uma característica muito importante que o SSIS possui, a parametrização. Com ela podemos alterar o comportamento do ETL a cada execução, sem haver a necessidade de fazer alterações no projeto e novas publicações. Basicamente, podemos dividir a parametrização em package level e project level, ou seja, os parâmetros podem ser criados e definidos para um único pacote ou para todos os pacotes do projeto.

Para criar, modificar ou excluir parâmetros no project level, é necessário utilizar a janela Project.params, conforme as figuras abaixo:

Depois de aberta a janela, definimos o nome do parâmetro, tipo de dado que será gravado, valor, obrigatoriedade e como opcional, é possível definir uma descrição.

Devemos tomar cuidado na hora de definir as propriedades Sensitive Required. Se for definido como sensível, seu valor vai ser gravado com criptografia e não poderá ser consultado por T-SQL. Já a propriedade Required, define que o parâmetro é obrigatório, ou seja, o projeto não executa sem a definição de um valor.

Os parâmetros package level, como o próprio nome já diz, são definidos em cada package do projeto. Todas as definições são feitas utilizando a aba Parameters, conforme a figura abaixo:

As propriedades seguem as mesmas configurações dos parâmetros que são criados no project level. A única diferença é que os parâmetros são definidos somente para um pacote.

O Data Tools define que podem ser atribuídos três tipos de valores aos parâmetros: Execution ValueServer Value e Design Value. Abaixo vamos descrever os três casos:

Server Value

O valor do parâmetro pode ser definido após a publicação do projeto, utilizando o SSMS e acessando as configurações do catálogo, conforme as figuras abaixo:

Execution Value

O valor vai ser definido somente para a instância do projeto que vai ser executada. Esse valor pode ser mantido para todas as execuções ou pode ser alterado a cada execução. Podemos definir os valores utilizando as próprias stored procedures do banco SSISDB, no exemplo abaixo, foram utilizadas as procedures create_execution e set_execution_parameter_value:

DECLARE @ExecutionID INT;

EXEC [SSISDB].[catalog].[create_execution]
    @package_name = 'pckExemplo.dtsx',
    @folder_name = 'parametrizacao',
    @project_name = 'parametrizacao',       
    @execution_id = @ExecutionID OUTPUT;


EXEC [SSISDB].[catalog].[set_execution_parameter_value]
    @execution_id = @ExecutionID,
    @object_type = 50,
    @parameter_name = N'SYNCHRONIZED',
    @parameter_value = 2
EXEC [SSISDB].[catalog].[start_execution]
    @execution_id = @ExecutionID;

Para mais informações sobre as duas procedures, você pode consultar a documentação completa das procedures nos links abaixo:

https://docs.microsoft.com/en-us/sql/integration-services/system-stored-procedures/catalog-create-execution-ssisdb-database?view=sql-server-ver15

https://docs.microsoft.com/en-us/sql/integration-services/system-stored-procedures/catalog-set-execution-parameter-value-ssisdb-database?view=sql-server-ver15

Design Value

O valor é definido no momento que o parâmetro é criado utilizando o Data Tools permanecendo igual durante todas as execuções até ser feita uma alteração.

Vamos exemplificar a utilização de parâmetros para alterar o server name de uma conexão do tipo OLEDB. Primeiro devemos criar o parâmetro e nesse caso foi criado a nível do projeto utilizando um valor fixo, conforme a figura abaixo:

Para atribuir este parâmetro a conexão, devemos selecionar a opção parameterize e após abrir a janela com as opções, selecionamos a propriedade desejada e o parâmetro anteriormente criado:

Existem inúmeras possibilidades para utilizar os parâmetros. Podem ser utilizados para definir dados de conexão, para incluir valores no Data Flow e em muitos casos podemos alterar completamente o funcionamento do ETL. Cada projeto possui as suas características de funcionamento, sendo necessário uma análise antes de implementar qualquer parâmetro.

Esperamos que esse post tenha ajudado. Qualquer dúvida, estamos à disposição!