Usando Expressions dentro do Foreach Loop Container para Captura de Dados de arquivos específicos em um diretório

Na semana passada alguns alunos de um curso de Integration Services que ministrei me trouxeram um questionamento bem interessante sobre um problema com relação à criação de processos de ETL.

Eles trabalham na Caixa Econômica Federal e lá possuem uma situação onde necessitam extrair de um diretório de rede – onde vários arquivos são armazenados de vários processamentos distintos – dados de apenas um arquivo relacionado ao dia útil anterior de um processo específico.

Achei o caso bem interessante e através desse post vou demonstrar como resolver a issue.


Criei uma série de arquivos de para simular o ambiente dos alunos. Nesse diretório teremos dois tipos de arquivos distintos pelo nome que representam dois processos diferentes. são eles [NPD010253.DOC2794.031016007.ARC6576] e [JJX010253.DOC2794.031016007.ARC1076]. Os arquivos são formados pela sigla do processo e uma numeração específica do processo [NPD010253] + o número do documento [DOC2794] + a data dos dados acompanhada de um outro código [031016007] + um outro código e numeração [ARC6576] (toda a nomeclatura do arquivos é meramente demonstrativa).

02

Entendido isso, a problemática é a seguinte: é necessário que desse diretório onde se encontram arquivos de todos os dias úteis do mês de Outubro para ambos os processos (processo [NPD] e processo [JJX]) sejam extraídos somente os dados do arquivo do processo [NPD] porém somente os dados do último dia útil do mês (Lembrando que esse processo hipotético somente será executado de segunda a sexta feira).


Use o Script abaixo para criação do banco de dados de teste que será usado no artigo e acesse o Link para realizar o download do pacote e arquivos usados na demonstração. Para que não ocorram erros, ponham a pasta de arquivos baixados dentro do diretório C:\.

--------------------------------------------
---------- CRIA AMBIENTE DE TESTE ----------
--------------------------------------------
USE master
GO

CREATE DATABASE test_SSIS
GO 

CREATE TABLE tbCliente (
 CodCliente INT,
 NomCLiente VARCHAR(255),
 TipoCliente VARCHAR(55),
 EmailCliente VARCHAR(255),
 TipoTelefone VARCHAR(55),
 TelefoneCliente VARCHAR(55),
 TipoEndereco VARCHAR (55),
 EnderecoCliente VARCHAR (255),
 CodigoPostal VARCHAR (50),
 DatCriacaoRegistro DATETIME
 )

o Pacote nada mais é do que um fluxo de ETL para carga dinâmica de vários arquivos distintos usando o Foreach Loop Container e a parametrização da ConnectionString da conexão com o Flat File Source usando a variável que recebe o nome do arquivos dentro do loop. 

Se ao ler este artigo você ainda não estiver familiarizado com o uso do Foreach Loop Container para carga em loop de vários arquivos em um mesmo destino podem me mandar email que explico como funciona pontualmente já que explicar essa configuração não é a ideia do artigo.

01

A segunda etapa de configuração que será demonstrada abaixo ainda não existe no pacote que está disponível para download. Isto é proposital para que você possa seguir o passo-a-passo de configuração do post.


Para que o ETL carregue apenas os dados do arquivo do último dia útil do mês (sempre o dia útil anterior ao dia atual) e apenas de um dos processos disponíveis no diretório é necessário que, dentro do Foreach Loop Container haja uma parametrização da opção Files: dentro da Aba de configuração Collection.

Para isso, será necessário que você utilize uma Expression. Essa expressão será mapeada no campo Expressions que fica abaixo da seleção de Enumerador do foreach [Enumerator].

03

Dentro da janela de propriedades você irá selecionar em Property a opção FileSpec, na opção Expression, você irá preencher com a expressão abaixo (é possível também, dentro de Property, usar a opção Directory para mapear o diretório do arquivo dinamicamente):

"NPD*" + (
 LEN((DT_WSTR, 2) DAY((DT_DATE) "2016-10-24")) == 1 ? 
 "0" + ((DT_WSTR, 2)(
 DATEPART("DW",(DT_DATE)"2016-10-24") == 2 ? 
 (DT_WSTR, 2)(DAY((DT_DATE)"2016-10-24") -3) :
 (DT_WSTR, 2)(DAY((DT_DATE)"2016-10-24"))
 )
 ) : (
 (DT_WSTR, 2)(DATEPART("DW",(DT_DATE)"2016-10-24") == 2 ? 
 (DT_WSTR, 2)(DAY((DT_DATE)"2016-10-24") -3) :
 (DT_WSTR, 2)(DAY((DT_DATE)"2016-10-24"))
 )
 )
+ (LEN((DT_WSTR, 2) (MONTH((DT_DATE) "2016-10-24"))) == 1 ? 
 "0" + (DT_WSTR, 2) (MONTH((DT_DATE) "2016-10-24")) : 
 (DT_WSTR, 2) (MONTH((DT_DATE) "2016-10-24"))) 
 + (RIGHT((DT_WSTR, 4) (YEAR((DT_DATE) "2016-10-24")),2)) 
 )+ "*.*"

05

Essa expressão irá criar a busca dinâmica pelo nome do arquivo que possua o identificador do processo [NPD] e que tenha data igual ao dia de hoje – 1 (último dia útil).

08

Obs – A expressão acima usa a data ’24-10-2016′ de maneira não dinâmica. Isso está dessa maneira porque o último arquivo do diretório possui a data ’21-10-2016′. Para que em ambiente de produção isso ocorra dinamicamente substitua a data ’24-10-2016′ pela Function GETDATE() conforme a Expression abaixo:

"NPD*" + (
 LEN((DT_WSTR, 2) (DAY(GETDATE()))) == 1 ? 
 "0" + ((DT_WSTR, 2)(
 DATEPART("DW",(GETDATE())) == 2 ? 
 (DT_WSTR, 2)((DAY(GETDATE())) -3) :
 (DT_WSTR, 2)((DAY(GETDATE())))
 )
 ) : (
 (DT_WSTR, 2)(DATEPART("DW",(GETDATE())) == 2 ? 
 (DT_WSTR, 2)(((DAY(GETDATE()))) -3) :
 (DT_WSTR, 2)((DAY(GETDATE())))
 )
 )
+ (LEN((DT_WSTR, 2) (MONTH(GETDATE()))) == 1 ? 
 "0" + (DT_WSTR, 2) (MONTH(GETDATE())) : 
 (DT_WSTR, 2) (MONTH(GETDATE()))) 
 + (RIGHT((DT_WSTR, 4) (YEAR(GETDATE())),2)) 
 )+ "*.*"

Agora, ao executarmos o pacote com o breakpoint habilitado para cada interação do loop poderemos observar que a variável nomeArquivo que, dentro do Flat File Connection, irá se tornar a Connection String para o ETL irá está mapeando o arquivo de nome [NPD010253.DOC2794.211016008.ARC6590.txt] cujo processo corresponde ao processo mapeado na Expression e a data diz respeito à data anterior à data mapeada.

06

Se continuada a execução do pacote, não haverão mais interações de Loop e o pacote irá terminar o processo de carga.

Se você for até o Management Studio e executar a query abaixo, verá que a única data existente dentro da tabela é 2016-10-21.

SELECT DISTINCT CAST(DatCriacaoRegistro 
 AS DATE) AS DatCriacaoRegistro
FROM tbCliente

07


É claro que a Expression deverá ser adaptada para cada ambiente e cada nomeclatura de arquivos segundo as necessidades do processo de carga de ETL.

Espero poder ajudá-los!

4 comentários sobre “Usando Expressions dentro do Foreach Loop Container para Captura de Dados de arquivos específicos em um diretório

  1. Muito bom post, Arthur!
    Esse tipo de pacote de ETL é muito útil para realizar carga de dados de sistemas do governo, por exemplo, onde é necessários ler vários arquivos.
    Dependendo da necessidade, pode ser interessante também utilizar uma variável do SSIS para receber o valor da data que se deseja realizar a carga, por exemplo, buscando a data da última inclusão realizada na tabela (ao invés de passar automaticamente a função GETDATE()). Assim é possível realizar o controle de execução e recarregar arquivos de cargas que tenham falhado.

    Curtido por 1 pessoa

    1. Marcos, Muito obrigado pelo feedback! Fico muito feliz em saber que estou conseguindo contribuir! 🙂

      Quanto a tua sugestão, acho completamente válida. A ideia do post é exatamente essa: Ser usado como um direcionador para outras soluções, dependendo do ambiente e do requisito do negócio.

      O post somente foi criado nessa dinâmica por ter sido uma dúvida dos alunos que atenderia diretamente a uma necessidade deles na Caixa!

      Mas é isso. Se você implementar algo parecido com o que disse, por favor, compartilha conosco! 😀

      Grande abraço!!!

      Curtir

      1. Olá Arthur, tudo bem?

        Achei importante comentar porque já participei de um projeto que tinha uma necessidade parecida, de carga de arquivos delimitados para um processo de BI.
        Vou colocar este tópico como uma ideia para um dos próximos posts do meu blog, certamente é um tema legal para explicar!
        A ideia é essa mesmo, compartilharmos experiências para ajudar quem está lendo a encontrar soluções.
        Abraços!

        Curtido por 1 pessoa

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