O que é o SSIS Excel Parser e qual a sua utilização em Business Intelligence?
O SSIS Excel Parser é um componente implementado via ferramenta SSIS (SQL Server Integration Services) e o seu principal objetivo é extrair dados de ficheiros Excel, de qualquer célula, para a base de dados, de uma maneira mais normalizada, onde mais tarde podem ser consultados via Vistas SQL (Views).
A ideia é implementar um “Template de Leitura” (Modelo) que permita ao componente saber que Folhas do ficheiro Excel devem ser tratadas e, em cada uma, que Células a tratar por sua vez (localizadas por uma posição absoluta: linha e coluna).
Depois, para cada um destes Templates/Modelos, podem-se gerar Instâncias desses mesmos ficheiros Excel, com os dados extraídos para Tabelas normalizadas.
Por exemplo, um “ficheiro template” pode ser modelado assim:
Por ordem:
Template – Cabeçalho
TemplateId – chave primária
Category – categoria
Name – título
ExcelFormatFile – “wild-card” de leitura do ficheiro
Description – descrição
Sheets4Template – Detalhe acerca das folhas a modelar
TemplateSheetId – chave primária
FK_Template – chave estrangeira para o template Excel
ExcelSheetName – nome da folha Excel
SetOrder – ordem da folha no ficheiro Excel (posição)
Description – descrição
Cont4Sheets – Detalhe acerca do conteúdo; células localizadas por uma linha e coluna
FK_TemplateSheet – chave estrangeira para a folha Excel
DataRegionName – permite agrupar células por nome (opcional)
FieldName – campo
Location_RowNr – linha
Location_ColumnNr – coluna
ExpectedType – tipo esperado
Description – descrição
Instâncias de Template
Modelo SQL:
Por ordem:
- Chave da Instância
- Chave estrangeira para o Template
- Nome da Instância
- Data de Movimento
- Nome Ficheiro Excel
- Data Inserção
- Permite histórico de extrações
Por ordem:
- Chave primária
- Chave estrangeira para Instância
- Campo
- Valor
Modelar o “Template”:
A seguinte Vista SQL foi criada para facilitar na leitura dos “Templates” modelados:
Instâncias:
A seguinte Vista SQL foi criada para facilitar a leitura dos dados extraídos associados às variadas Instâncias:
Vamos testar com um exemplo!
Primeiro, o Modelo
Exemplo possível de ficheiro Excel em baixo:
Nota: o nome do ficheiro tem o ano (2014) e o nome da instância (TEGEE01)
Usa-se um ficheiro Excel para auxiliar na configuração dos Templates, em SQL:
Comandos TSQL são gerados para facilitar a inserção dos registos nas tabelas apontadas acima:
Depois, é preciso criar o pacote SSIS (SQL Integration Services)
Cada Modelo/Template deve ser implementado por um pacote SSIS (pode ser otimizado no futuro).
No nosso exemplo, podemos criar o seguinte pacote SSIS:
Uma “script task” codificada em C# é usada para ler os dados do ficheiro Excel.
O pacote possui uma variável interna que identifica qual o Template a usar:
Se um novo ficheiro precisar de ser modelado, o que devemos fazer?
1- Identificar nesse ficheiro Excel-fonte:
a. As folhas Excel a modelar
b. E em cada folha, quais as células a ler
2- Usar o ficheiro Excel auxiliar para gerar comandos TSQL de inserção do Modelo
3- Criar um novo pacote SSI que irá tratar este novo modelo (copy-paste do já existente e adaptá-lo mudando a variável interna. Este comportamento poderá ser melhorado)
É isto.
