Usando o SSIS para importar várias planilhas de um mesmo arquivo Excel para o SQL Server

Boa Noite Pessoal,

Essa semana vou ministrar uma turma de Integration Services 2008 (SSIS). Aproveitando para revisar o material e ensaiando alguns scripts extras, é nítido o quanto ele é superior ao seu antecessor o Data Transformation Services (DTS). Estive conversando com um colega em um dos clientes que presto consultoria sobre o SSIS já que ele o utiliza bastante. Um dos desafios comuns que ele presencia (e estou certo que muitos outros) é a importação de planilhas excel.

Obviamente, importar os dados de uma planilha excel para um banco SQL Server (supondo que a planilha esteja estruturada) é sem dúvida algo muito simples de se fazer (tanto que existe um Wizard disponível para isso). O desafio não é a importação em si, mas o  fato de haver várias planilhas em um mesmo arquivo. Imagine um arquivo XLS (ou XLSX) com a relação de empregados de cada departamento. Supondo que exista um número de departamentos desconhecido, é fácil importar esses empregados, mas como fazer para que o pacote leia cada planilha de forma dinâmica ? E se fosse uma planilha com as cotações de vários ativos ? Como fazer para criar um pacote que importe os dados de cada ativo ? Pode-se criar um pacote que leia as planilhas de forma estática, mas e se novos departamentos (ou ativos) forem retirados ? E se novos surgirem ? Nesse caso, inevitavelmente o pacote irá apresentar erros e (ou) não irá importar todos os dados necessários.

Para demonstrar como fazer essa importação de forma dinâmica, utilizarei uma planilha fictícia com os dados de alguns produtos de alguns fornecedores. Essa planilha está disponível no link ao final do artigo juntamente com os projetos SSIS. A planilha possui a relação de produtos adquiridos de três fornecedores (Saraiva, Submarino e Cultura). O objetivo é importar as compras registradas dos três fornecedores dinamicamente, ou seja, o pacote SSIS não deve saber previamente quem são ou quanto são os fornecedores.

O script abaixo cria um banco de dados e uma tabela para receber os dados dos fornecedores no arquivo XLS.

— Cria o banco de dados
CREATE DATABASE Produtos

— Muda o contexto do banco de dados
USE Produtos;

— Cria a tabela de produtos
CREATE TABLE ProdutosAdquiridos (
    Produto VARCHAR(150), Categoria VARCHAR(50),
    Quantidade TINYINT, Preco SMALLMONEY,
    DataCompra SMALLDATETIME, Fornecedor VARCHAR(50))

O próximo passo é a criação de um pacote SSIS para a importação dos dados das planilhas no arquivo XLS. Os passos para criação do pacote são bem simples (um projeto SSIS no BIDS). Como haverá iteração pelas planilhas é necessário utilizar um container do tipo ForEachLoop. As seguintes propriedades desse container devem ser configuradas:

Aba Propriedades
General Name: LoopXls
Description: Realiza um loop para ler os nomes das planilhas
Collection Enumerator: Foreach ADO.NET Schema Rowset Enumerator
Connection: New Connection
    Connection
        Provider: Microsoft Jet 4.0 OLE DB Provider
        Database file name: Localize o arquivo Xls (é xls e não access)
        User name: Deixe em branco
        Password: Deixe em branco
    All
        Extended Properties: Excel 8.0;HDR=Yes
Schema: Tables
Variable Mappings Variable: New Variable
    Container: LoopXls (ou ForEach Loop Container)
    Name: PlanilhaXLS
    Value Type: String
    Value: Submarino$
Index: 2

O valor "Excel 8.0;HDR=Yes" é necessária para que o Provider saiba que se trata de um arquivo Excel e HDR significa que a primeira linha (HEADER) possui o nome das colunas. O valor "Submarino$" é necessário apenas para configurar a migração de dados já que existe uma planilha chamada "Submarino". Posteriormente ele não tem nenhum efeito na execução do pacote. Depois que o pacote estiver salvo, nem é necessário que exista um fornecedor "Submarino".

Dentro do container é preciso inserir uma tarefa DataFlow para migrar os dados das planilhas. No exemplo, a propriedade Name foi configurada para "Fornecedor" e a propriedade Description foi configurada para "Importa Dados". A figura abaixo mostra a exibição do container LoopXls e a tarefa DataFlow (apenas em Control Flow).

Após a configuração do container e na tarefa em Control Flow, é necessário configurar a tarefa "Fornecedor" em Data Flow. A primeira "Data Flow Source" desse "Data Flow" é o Excel Source. As seguintes propriedades devem ser configuradas:

Aba Propriedades
Connection Manager

OLE DB connection manager (New)
    Excel File Path: Localize o arquivo Xls
Data Access Mode: Table name or view name variable
Variable name: User::PlanilhaXLS

Properties Name: Planilha Excel

Como as planilhas não possuem uma coluna com o nome do fornecedor, não será possível importar diretamente o nome do fornecedor da mesma forma que as demais colunas. A informação do nome do fornecedor está no nome da planilha (no arquivo Excel) e no nome da variável (no SSIS). Para que a importação do nome do fornecedor possa ser feita, é preciso criar uma coluna com o valor da variável (sem o "$"). Isso pode ser feito com a Data Transformation Flow "Derived Column" que como saída da tarefa anterior deve possuir as seguintes propriedades:

Aba Propriedades
Derived Column Transformation Editor

Derived Column Name: Fornecedor
Derived Column: <add as new column>
Expression: REPLACE(@[User::PlanilhaXLS],"$","")
Data Type: string [DT_STR]
Length: 50
Code Page: 152 (ANSI – Latin I)

Properties Name: Coluna Fornecedor

Como existem certas diferenças entre os tipos do Excel e os tipos do SQL Server é necessário fazer algumas conversões antes de propriamente importar os dados. A Data Flow Transformation "Data Convertion" pode equalizar essas diferenças e permitir a importação. As seguintes propriedades devem ser configuradas:

Aba Propriedades
Data Conversion Transformation Editor

Input Column: Produto
Output Alias: ProdutoConvertido
Data Type: string [DT_STR]
Length: 150
Code Page: 1252 (ANSI – Latin I)

Input Column: Categoria
Output Alias: CategoriaConvertida
Data Type: string [DT_STR]
Length: 50
Code Page: 1252 (ANSI – Latin I)

Properties Name: Conversão de Colunas

Após a configuração das conversões necessárias é possível exportar os dados para um banco de dados SQL Server através de uma Data Flow Destination "OLE DB Destination":

Aba Propriedades
Connection Manager

OLE DB Connection Manager (Clique em New duas vezes)
    Provider: Native OLE DB\SQL Native Client
    Server Name: Digite o nome do servidor SQL
    Logon on to the server: Configure a autenticação
    Select or enter a database name: Produtos
Data Access mode: Table or View
Name of the table or the view:[dbo].[ProdutosAdquiridos]

Mappings

Input ColumnDestination Column
ProdutoConvertido – Produto
CategoriaConvertida – Categoria
Quantidade – Quantidade
Preco – Preco
DataCompra – DataCompra
Fornecedor – Fornecedor

Se tudo tiver sido configurado conforme o esperado, o Data Flow deve assemelhar-se à figura abaixo:

Agora que o pacote está devidamente configurado, basta executá-lo, conectar-se ao servidor SQL e verificar o resultado das importações. Caso algum problema tenha ocorrido, disponibilizo o projeto SSIS juntamente com a planilha para acompanhamento no link abaixo:

http://cid-f4f5c630410b9865.skydrive.live.com/self.aspx/ProjetosSQLServer/20090531%7C_ImportarXLS.zip

Agora é hora de dormir, pois, a aula é amanhã cedo. Aos que desejarem algo ainda mais avançado (vários arquivos e várias planilhas), sugiro a leitura do link abaixo:

How to: Loop through Excel Files and Tables by Using a Foreach Loop Container
http://technet.microsoft.com/en-us/library/ms345182.aspx

[ ]s,

Gustavo

2 Respostas para “Usando o SSIS para importar várias planilhas de um mesmo arquivo Excel para o SQL Server

  1. Tive um problema.no passo Data Transformation Flow "Derived Column" não consegui inserir a coluna Fornecedor, pois não consegui alterar "Data Type" para "string [DT_STR]"de resto tudo okabs e excelente post.

  2. Oi Vagner,Vou refazê-lo e atualizarei a postagem. Você usou o 2005 ou 2008 ?[ ]s,

Deixe uma 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 )

Imagem do Twitter

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

Foto do Facebook

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

Foto do Google+

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

Conectando a %s