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 um comentário

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