Usando Variáveis de Ambientes para Parametrizar conexões no SSIS

Neste post você aprenderá como parametrizar conexões para as bases de dados usando ambientes dentro do SSISDB.

Dessa maneira é possível executar o mesmo pacote em bancos de dados distintos, eliminando assim, a necessidade de alterar o pacote entre ambientes – Desenvolvimento, Homologação e Produção, por exemplo.

IMPORTANTE – Este post somente será válido para quem tem um conhecimento intermediário tanto de SQL Server quanto de Integração de Dados usando o Integration Services.


Em outras ferramentas de Integração de Dados e ETL é possível que você crie um arquivo em um diretório específico com os parâmetros de conexão do Servidor/Banco de Dados, para que, ao executar o pacote, ele capture as credenciais do devido ambiente.

Nas versões anteriores do Integration Services também era possível configurar dessa mesma forma – através de um arquivo .xml – a conexão com os devidos ambientes para execução do pacote.

Porém, para eliminar a possibilidade de erros (um arquivo fica vulnerável à exclusão ou alteração), a partir do SQL Server 2012, é possível criar Ambientes dentro do banco de dados responsável pelo armazenamento dos projetos e pacotes do SSIS, o SSISBD.


Antes de você iniciar o aprendizado, será necessário que você possua a base AdventureWorks2014 devidamente configurada em sua instancia e que você execute o script abaixo. Ele será responsável por criar as bases de dados de AdventureWorks_Homolog e AdventureWorks_Prod.

--Cria a base de dados [AdventureWorks_Homolog]
USE master
GO 

CREATE DATABASE [AdventureWorks_Homolog]
GO

--Cria a a tabela [Person] na base [AdventureWorks_Homolog]
USE [AdventureWorks_Homolog]
GO

CREATE TABLE [dbo].[Person](
 [BusinessEntityID] [int] NULL,
 [PersonType] [nchar](2) NULL,
 [NameStyle] [bit] NULL,
 [Title] [nvarchar](8) NULL,
 [FirstName] [nvarchar](50) NULL,
 [MiddleName] [nvarchar](50) NULL,
 [LastName] [nvarchar](50) NULL,
 [Suffix] [nvarchar](10) NULL,
 [EmailPromotion] [int] NULL,
 [rowguid] [uniqueidentifier] NULL,
 [ModifiedDate] [datetime] NULL
) ON [PRIMARY]
GO

--Cria a base de dados [AdventureWorks_Prod]
Use master
GO

CREATE DATABASE [AdventureWorks_Prod]
GO

--Cria a a tabela [Person] na base [AdventureWorks_Prod]
USE [AdventureWorks_Prod]
GO

CREATE TABLE [dbo].[Person](
 [BusinessEntityID] [int] NULL,
 [PersonType] [nchar](2) NULL,
 [NameStyle] [bit] NULL,
 [Title] [nvarchar](8) NULL,
 [FirstName] [nvarchar](50) NULL,
 [MiddleName] [nvarchar](50) NULL,
 [LastName] [nvarchar](50) NULL,
 [Suffix] [nvarchar](10) NULL,
 [EmailPromotion] [int] NULL,
 [rowguid] [uniqueidentifier] NULL,
 [ModifiedDate] [datetime] NULL
) ON [PRIMARY]
GO

Será necessário também que você crie um projeto, faça o Download do packege que será configurado aqui e adicione esse pacote dentro do projeto que criou.


Agora que o ambiente está pronto, você irá primeiramente criar o SSISDB, dendro da instancia do SQL Server. Para isso, clique com o botão direito em Integration Services Catalog e escolha a opção Create Catalog.

catalog1

Na pagina de configuração deixe tudo default e atribua uma senha para o Catalog.

catalog2

Feito isso, o banco de dados SSISDB estará criado dentro da instancia do SQL Server.

Agora, dentro do projeto do SSIS, você irá criar um project parameter. Para isso, acesse a aba parameter ou, dentro do Solution Explore, acesse Project.params.

01

Crie um parâmetro chamado AdventureWorks_Environment com o tipo de dado String e a opção Required como True. Fazendo isso, você está determinando que esse parâmetro deve ser usado obrigatoriamente quando o pacote do SSIS for executado.

02

Agora que o parâmetro está criado, você irá parametrizar a conexão denominada AdventureWorks_Environment. Para isso, clique com o botão direito na conexão e selecione a opção Parametrize.

03

Na aba de configuração, selecione a opção Use Existing Parameter e escolha o parâmetro que você acabou de criar no passo anterior.

04

O pacote apresentará erro. Isso é normal. O erro ocorre porque essa conexão não tem uma ConnectionString configurada. Isso será realizado daqui a pouco através do SSISDB.

O próximo passo é realizar o deploy desse projeto para dentro do SSISDB que você criou anteriormente. Para isso, na aba de Solution Explore, clique com o botão direito no projeto e depois em Deploy.

07

Selecione o servidor localhost e crie uma pasta para armazenar o projeto dentro do banco de dados. Depois realize o deploy.

Feito isso, o SSISBD passará a armazenar o projeto.

09

O próximo passo será realizar a criação dos dois ambientes dentro do SSISDB. Para isso, selecione com o botão direito a pasta Environment e selecione a opção Create Environment.

10

Crie dois ambientes. Um com o nome Homolog e o outro com o nome Prod conforme a figura abaixo.

11

Abra o primeiro ambiente que foi criado, acesse a aba Variables e crie uma variável de nome AdventureWorks_Environment, com o tipo de dados String e adicione dentro do campo Value a ConnectionString abaixo:


DataSource=.;InitialCatalog=AdventureWorks_Homolog;Provider=SQLNCLI11.1;Integated Security=SSPI;

Faça o mesmo para o ambiente Prod, porém, no campo Value, use a ConnectionString abaixo:


DataSource=.;InitialCatalog=AdventureWorks_Prod;Provider=SQLNCLI11.1;Integated Security=SSPI;

O próximo passo agora é referenciar os dois ambientes no pacote. Você fará isso clicando com o botão direito no Projeto e selecionando a opção Configure. Vá até a aba References e Adicione os dois ambientes que foram criados – Homolog e Prod.

13

Feito isso, navegue até a aba Parameters e, no campo Value do parâmetro do pacote, referencie a Variável que foi criada nos dois ambientes de nome AdventureWorks_Environment.

15

Feito isso, sua aba de configurações ficará igual a imagem abaixo.

14

Depois de criados os ambientes e suas devidas variáveis e de apontar essas variáveis de nome igual para o parâmetro do pacote, você criará dois Jobs para execução da carga. Um de nome Homolog e outro com o nome Prod.

Configure o Step do pacote conforme imagem abaixo:

Job1

Clique na aba Configuration, marque o checkbox Environment e selecione o ambiente Homolog.

job2

Finalize a criação do Job Homolog. Faça o mesmo para a criação do Job Prod, porém, selecione o Ambiente Prod.

Feito isso, execute o Job Homolog. Ao executar um SELECT nas duas tabelas Person – Banco de dados AdventureWorks_Homolog e AdventureWorks_Prod – você perceberá que somente a tabela do banco de homologação recebou dados.

job4

Execute o Job Prod e, ao finalizar a execução, realize os meus dois SELECTs nas tabelas Person das bases de Homologação e Produção. Você perceberá que agora somente a tabela da base AdventureWorks_Prod recebeu dados.

Job5


Essa é a maneira mais simples e segura de trabalhar com o mesmo pacote em vários ambientes, evitando a possibilidade de erros de Deploy ou de execução de teste em um ambiente errado – de Podução, por exemplo.

3 comentários sobre “Usando Variáveis de Ambientes para Parametrizar conexões no SSIS

Deixe uma resposta para Marcos Paiva Cancelar resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair /  Alterar )

Foto do Google

Você está comentando utilizando sua conta Google. Sair /  Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair /  Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair /  Alterar )

Conectando a %s