Fala chefia, tudo blz?
Hoje vamos avançar em mais um mega assunto em nossa série não Mexa Nos Meus Componentes!
A estrela de hoje será o componente ForEach Loop Container, um componente que possui como finalidade a capacidade de ler um diretório, mapear todos os arquivos e colocá-los em uma fila de processamento.
Quer saber mais? Segue então aqui comigo!
Antes de avançar para o código, vamos fazer uma breve introdução de como iremos trabalhar nesse post.
1 - Criação de um projeto SSIS - Sempre que trabalhamos com SSIS, existe a necessidade de realizar a criação de um projeto no Visual Studio, caso tenha criado nos posts anteriores, pode utilizar sem problemas, porém caso não tenha trate de criar pois é um item obrigatório! Dentro desse projeto vamos armazenar todos os pacotes de ETL;
2 - Criação de um pacote de ETL - Quando falamos em pacotes de ETL, estamos nos referindo ao conjunto de fluxos que será usado para trafegar com a nossa informação da sua origem até seu destino. Recomento que caso esteja acompanhando nossa série, crie um pacote para cada item, fica mais fácil o entendimento;
3 - Criando/Configurando as conexões - Vamos utilizar nesse nosso post duas conexões, uma do tipo Flat File Source pois vamos consumir um arquivo (Source) e uma do tipo OLE DB pois iremos consumir e gravar dados do Microsoft SQL Server (Destinantion)Esse é o passo onde definimos as conexão;
4 - Criando nosso Fluxo de Dados
4.1 - Componentes que serão usados - Nesse exemplo, vamos trabalhar de uma forma diferente do outros posts. Pois sempre apresentamos os componentes do Data Flow, nesse post a cereja do bolo está no Control Flow e diante disso vamos usar um componente de Execute SQL Task, que será usado para limpar a tabela, um ForEach Loop Container para realizar a leitura do diretório onde estão os arquivos, um componente de Data Flow que será responsável pelo fluxo de importação do arquivo até o destino e o componente File System Task, esse componente que será o responsável por realizar a movimentação dos arquivos importados;
Bora começar então!
1 - Criando um Projeto no SQL Server Integration Services
Vou considerar que você não tem nenhum projeto criado no Visual Studio do SQL Server Integration Service. Um ponto bem importante desse estudo é que não irei falar como que você realiza a configuração do seu ambiente, entenderei que você já tenha feito isso em outro momento.
Para criar um projeto é bem simples. Vamos abrir o Visual Studio e seguir os próximos passos.
(1) Selecionar File;
(2) New;
(3) Project;
(4) Caso tenha um projeto criado basta clicar em Open Project.
Após clicar em New Project, a tela abaixo será apresentada.
(1) Escolher Integration Services;
(2) Integration Services Project;
(3) Informar um nome para o Projeto;
(4) Definir o local (diretório) que o projeto será gerado;
(5) Informar uma nomenclatura para a sua solução;
(6) Caso queira que um diretório para o projeto seja criado basta marcar essa opção;
(7) Clicar em OK para realizar a criação;
2 - Criando nosso Pacote de ETL
Primeiro ação será a criação de um pacote do SSIS. Essa criação é bem simples, acompanha comigo.
(1) Clicar com o botão direito do mouse sobre SSIS Package;
(2) Clicar em New SSIS Package;
Além dessa possibilidade, outras ações podem ser realizadas.
Importar um Arquivo dtsx - basta clicar em Add Existng Package;
Realizar o Upgrade dos seus pacotes - basta clicar sobre Convert Deployment Model ou Upgrade All Package;
Realizar um Import and Export - basta clicar sobre SSIS Import and Export Wizard;
Ordenar seus pacotes - basta clicar em Sort by name.
Após a criação do nosso arquivo dtsx, vamos para o desenho do nosso fluxo.
(1) Nome do arquivo dtsx;
Para alterar basta clicar sobre ele e apertar o botão F4 e em file name informar o nome do seu pacote.
Ou clicar sobre o pacote e apertar F2
(2) Aba Control Flow;
(3) Inserir um componente de Data Flow Task;
(4) Realizar duplo clique ou clicar em Data Flow.
Ao clicar em Data Flow, vamos começar a brincadeira! Dessa vez vamos fazer iguais aos filmes de Hollywood começando do final. Apresento para você como que será o resultado final desse nosso trabalho (1).
Vamos trabalhar então para produzir mais esse conteúdo.
3 - Criando as Conexões
Primeiro passo será realizar a configuração das nossas conexões.
(1) Aba Connection Manage;
(2) Clicar com o botão direito do mouse e escolher New OLE DB Connection;
(1) Na aba Connection Manager, vamos clicar com o botão direito e selecionar OLE DB Connection Manager. Na tela que será apresentada, vamos clicar em New;
(2) Selecionar Native OLE DB\SQL Server Native Client 11.0;
(3) Informar o nome do servidor\instância;
(4) Selecionar o método de conexão;
(5) Selecionar o banco de dados. Clicando na setinha pra baixo é possível visualizar todos os bancos existentes em nosso servidor;
(6) Clicar em Test Connection para validar a conexão (Sempre comento que esse passo é desnecessário, pois ela já foi validada no passo anterior);
(7) Clicar em OK para finalizar.
(1) Aba Connection Manage;
(2) Clicar com o botão direito do mouse e escolher New Flat File Connection;
Após selecionar esse tipo de conexão, vamos partir para configuração.
(1) Informar um nome para a conexão;
(2) Primeira configuração será na aba General;
(3) Selecionar o local onde está o nosso arquivo;
(4) Configurações referente ao nosso arquivo (Se ele é delimitado ou não, se possui um qualificador de texto, se a primeira linha do arquivo contém ou não o cabeçalho das colunas etc);
(4) Clicar em OK para confirmar.
4.1 - Configurando o Componente Source Assistant
Conforme imagem anterior, vamos fazer uso de 3 componentes! Um para a nossa origem de dados e um para o destino dos dados.
(1) Em SSIS Toolbox, vamos selecionar o componente Source Assistant;
(2) Feito isso a tela ao centro será carregada;
(3) Como nossa origem será um arquivo, vamos selecionar Flat File;
(4) Selecionar a conexão que criamos;
(5) Observe que algumas informações referente a origem do arquivo é apresentada;
(6) Clicar em OK para confirmar.
4.3 - Configurando o Componente Destination Assistant
(1) Em SSIS Toolbox, vamos selecionar o componente Destination Assistant;
(2) Feito isso a tela ao centro será carregada;
(3) Como nossa origem será um arquivo, vamos selecionar SQL Server;
(4) Selecionar a conexão que criamos;
(5) Observe que algumas informações referente ao destino do arquivo é apresentada;
(6) Clicar em OK para confirmar.
Após a inclusão do componente de Source, vamos selecionar de onde (tabela) os dados deverão ser carregados.
(1) Clicar duas vezes sobre o componente OLE DB Source;
(2) Na aba Connection Manager;
(3) Selecionar a conexão que criamos anteriormente;
(4) Nesse passo também é possível realizar a criação de uma nova conexão;
(5) Nesse ponto podemos selecionar se a nossa origem será uma tabela ou view, caso escolha, no passo seguinte você deverá selecionar em uma lista qual a origem e também pode ser selecionado um script, caso seja escolhida essa opção, no passo seguinte você deverá informar o script de consulta;
(6) Selecionar a origem, ou tabela ou script, depende do passo anterior;
(7) É possível visualizar uma prévia dos dados;
(8) E clicar em OK para finalizar.
Agora vamos configurar o nosso Destination.
(1) Após dois cliques no componente Destination, uma tela semelhante a de baixo será carregada, vamos clicar em Mappings;
(2) A origem e destino serão apresentadas;
(3) Origem dos dados;
(4) Destino dos dados;
(5) Alterei o mapeamento para carregar as colunas criadas no passo anterior
(6) Clicar em OK para finalizar.
Observem que quando clicamos na aba Mappings, as colunas já serão carregadas com o mapeamento feito. Isso ocorre justamente porque por default, o mapeamento é feito sempre por igualdade de nome. Mais ele pode ser alterado a qualquer momento.
4.4 - Configurando Variáveis
Na tela abaixo vamos realizar a criação de algumas variáveis que serão usadas no processo de carga dos arquivos.
A principio vamos definir 4 variáveis:
arquivoCompleto
diretorioOrigem
diretorioImportado
arquivo
Vamos falar agora um pouco sobre essas variáveis.
arquivoCompleto - Essa variável é definida sem nenhum valor e será preenchida durante a leitura realizada pelo componente ForEachLoop;
diretorioOrigem - Essa variável será definida com o diretório onde os arquivos estarão disponíveis;
diretorioImportado - Essa variável será definida com o diretório para onde os arquivos serão movimentados;
arquivo - Essa variável será usada com um pedaço do nome do arquivo. Não informaremos o nome completo pois existem diversos arquivos nesse diretório, mais a frente você irá entender.
Vamos seguir explicando o que fizemos! Acompanha aqui comigo...
(1) Clicar na aba Variables;
(2) Área de definição das variáveis;
(3) Nome da variável;
(4) Escopo de criação da variável;
(4) Data type da variável;
(5) Valor que será definido para variável;
4.5 - Configurando o Componente Execute SQL Task
Após configurar as variáveis, vamos voltar para o Control Flow e realizar as configurações finais. Conforme ocorreu com o post anterior que falamos sobre o File System Task, o componente de principal desse post está no Control Flow porém precisamos primeiro importar o arquivo para depois aplicar as configurações do componente.
Vamos adicionar nesse momento o componente Execute SQL Task que será responsável por realizar a limpeza na tabela antes de importar um arquivo.
Segue aqui comigo...
(1) Clicar na aba Control Flow, depois clicar em SSIS Toolbox e selecionar o componente Execute SQL Task;
(2) Nome do componente;
(3) Nesse passo vamos configurar apenas a aba General do componente Execute SQL Task;
(4) Informar o nome desse passo dentro do nosso fluxo;
(5) Selecionar a conexão, vamos usar a que criamos anteriormente;
(6) Incluir um script de TRUNCATE TABLE;
(7) Clicar em OK para finalizar.
4.4 - Configurando o Componente Foreach Loop
Agora vamos falar um pouco sobre o componente principal do nosso post.
Nesse tópico vamos estar dando ênfase a um componente que será usado para ler um diretório, e de acordo com a configuração realizada, identificar todos os arquivos que estão nele.
Feito isso ele irá informar um arquivo de cada vez para a configuração da conexão que por sua vez irá carregar um arquivo por vez e no final o componente File System Task estará responsável por mover esse arquivo e o fluxo retorna para o inicio, ficando em um loop até que todos os arquivos existentes nesse diretório tenham sido processados.
Nos próximos passos vamos falar porque escolhemos cada arquivo. Vamos dar o pontapé inicial então!
Primeiro passo da configuração do componente será informar um nome para ele dentro do nosso fluxo.
(1) Na aba General;
(2) Em Name vamos informar o nome do componente;
Avançando para a aba Collection, vamos adicionar os parâmetros que serão usados para definir o local onde estão os arquivo que serão usados.
Na aba Collection vamos configurar dois parâmetros: Diretório (Directory) e Arquivo (FileSpec).
(1) Aba Collection;
(2) Em Expressions, vamos clicar no botão que aparece para informar a primeira variável;
(3) Em Property vamos selecionar Directory e clicar no quadradinho que tem em Expression;
(4) Selecionar a variável referente ao diretório onde estão os arquivos;
(5) Após localizar a variável basta arrastar até esse local;
(6) Clicamos em Evaluate Expression para verificar o valor da variável;
(7) Após o passo (6), o valor é apresentado nesse local;
(8) Estando tudo OK, clicar para prosseguir;
Nesse momento vamos configurar o nome do arquivo.
(1) Continuando na aba Collection;
(2) Em Expressions, vamos clicar no botão que aparece para informar a segunda variável;
(3) Em Property vamos selecionar FileSpec e clicar no quadradinho que tem em Expression;
(4) Selecionar a variável referente ao arquivo;
(5) Após localizar a variável basta arrastar até esse local;
(6) Clicamos em Evaluate Expression para verificar o valor da variável;
(7) Após o passo (6), o valor é apresentado nesse local;
(8) Estando tudo OK, clicar para prosseguir;
Agora vamos avançar para a aba Variable Mappings e selecionar a nossa variável que estará recebendo o caminho + o nome do arquivo.
Isso só é possível porque na tela anterior especificamos em Retrieve file name a opção Fully qualified. Quando definimos essa configuração, estamos informação ao componente que ele precisa concatenar o diretório + o nome do arquivo e formar um único valor.
Vamos ver como que fica!
(1) Aba Variable Mappings;
(2) Em Variable, vamos clicar na setinha pra baixo e selecionar nossa variável responsável por receber o nome completo da variável;
(3) Estando tudo OK, clicar para prosseguir;
Agora é o momento em que associamos o nome completo do nosso arquivo a nossa conexão com ele.
Isso é necessário pois como vamos carregar vários arquivos, nosso componente Flat File Connection, deve estar preparado para trabalhar dessa forma e a única forma disso acontecer é fazendo dessa forma.
É bem simples, segue comigo aqui.
(1) Clicar sobre a conexão com o arquivo, apertar o botão F4 e na janela Properties procurar por Expressions e clicar no quadradinho existente;
(2) Feito isso, a configuração é bem semelhante com a que fizemos anteriormente. Em Property vamos selecionar ConnectionString e em Expression clicar no quadradinho;
(3) Selecionar a variável arquivoCompleto e arrastar até esse local;
(4) Feito isso ela será definida;
(5) Estando tudo OK, clicar para prosseguir;
Processo de configuração do For Each finalizado. Vamos avançar agora para configurar o File System Task.
4.4 - Configurando o Componente File System Task
Vamos utilizar esse componente para realizar a movimentação do arquivo que foi importado para o diretório importado.
Esse passo é de extrema necessidade pois caso isso não seja feito, toda vez que o processo for executado ele estará importando sempre os mesmos arquivos.
Agora pensa em um cenário semelhante ao que possuo aqui na empresa que trabalho onde por dia são gerados em média 200 arquivos.
Se você não separar o que já foi processado do que ainda não foi processado, você ficará com um diretório gigantesco e processando dados que já foram processados.
Esse passo agora é o mesmo que realizamos no post anterior que falamos sobre o File System Task.
Vamos usar a operação Move File, ela será responsável por movimentar os arquivos importados do diretório de origem para o diretório importado. Bem simples!
(1) Clicar na aba Control Flow, depois clicar em SSIS Toolbox e selecionar o componente File System Task e realizar um duplo clique sobre ele;
(2) Na aba General;
(3) Como estaremos trabalhando com variável, em IsDestinationPathVariable vamos alterar de False pra True;
(4) Selecionar a variável que possui a informação do diretório que o arquivo deve ser movido;
(5) Alterar a opção OverwriteDestination de False para True. Essa configuração irá permitir que sempre que um arquivo com o mesmo nome existir no diretório de destino ele será sobrescrito;
(6) Informar um nome para o componente dentro do fluxo;
(7) Vamos selecionar a opção da operação que será realizada pelo componente, no nosso caso será Move File;
(8) De mesmo modo que realizamos no item (3) vamos alterar de False para True, informando que estaremos usando variável;
(9) Informar o nome da variável que possui o caminho completo do arquivo;
(10) Clicar em OK para finalizar.
Vamos alterar uma configuração do nosso componente.
(1) Clicar sobre o componente;
(2) Em DelayValidation, vamos alterar de False para True;
Vamos visualizar como que ficou o nosso fluxo.
Observe que a tabela está vazia, os arquivos ainda estão no diretório raiz.
(1) Fluxo;
(2) Tabela que receberá os dados;
(3) Arquivos que serão importados.
Pronto! Fluxo executado, tabela carregada e arquivos movimentados.
(1) Fluxo executado;
(2) Tabela carregada;
(3) Arquivos importados
Os arquivos usados até aqui estão disponíveis em:
Pacote SSIS - ForEach Loop Container
15_Importa_Cargos_ForEach_Loop_Container.dtsx
5 - Conclusão
Muito bem meu amigo, mais um post rico em conteúdo.
Hoje o objetivo foi trazer para você a combinação de dois componentes super bacanas onde um complementa o trabalho do outro.
Espero que tenha gostado e nos vemos no próximo post!
Grande abraço e fique com Deus!
Comments