Como fazer loop por meio de arquivos e tabelas do Excel usando um contêiner Loop Foreach

Crie uma variável de cadeia que receberá o caminho atual do Excel e nome de arquivo em cada iteração do loop. Para evitar problemas de validação, atribua um caminho de Excel válido e um nome de arquivo como o valor inicial da variável. (A expressão de amostra exibida mais adiante neste procedimento usa o nome da variável ExcelFile.)

Como alternativa, crie outra variável de cadeia que irá conter o valor do argumento Propriedades Estendidas da cadeia de conexão do Excel. Este argumento contém uma série de valores que especificam a versão do Excel, determinam se a primeira linha contém nomes de coluna e se o modo de importação é usado. (A expressão de amostra exibida mais adiante neste procedimento usa o nome de variável ExtProperties, com um valor inicial de “Excel 8.0;HDR=Yes”.)

Se você não usar uma variável para o argumento Propriedades Estendidas, adicione-a manualmente à expressão que contém a cadeia de conexão.

Adicione um contêiner Loop Foreach à guia Fluxo de Controle. Para obter informações sobre como configurar o contêiner Loop Foreach, consulte Como configurar um contêiner Loop Foreach.

Na página Coleção do Editor de Loop Foreach, selecione o enumerador de Arquivo Foreach, especifique a pasta na qual as pastas de trabalho do Excel ficam situadas e especifique o filtro de arquivo (geralmente * .xls).

Na página Mapeamento de Variáveis, mapeie Index 0 para uma variável de cadeia definida pelo usuário que receberá o caminho atual do Excel e o nome de arquivo em cada iteração do loop. (A expressão de amostra exibida mais adiante neste procedimento usa o nome da variável ExcelFile.)

Feche o Editor de Loop Foreach.

Adicione um gerenciador de conexões do Excel ao pacote conforme descrito em Como adicionar ou excluir um gerenciador de conexões em um pacote. Selecione uma pasta de trabalho existente do Excel para a conexão para evitar erros de validação.

Importante

Para evitar erros de validação conforme você configura tarefas e componentes de fluxo de dados que usam esse gerenciador de conexões do Excel, selecione uma pasta de trabalho existente do Excel no Editor do Gerenciador de Conexões do Excel. O gerenciador de conexões não usará esta pasta de trabalho em tempo de execução depois que você configurar uma expressão para a propriedade ConnectionString conforme descrito nas etapas a seguir. Após criar e configurar o pacote, você pode limpar o valor da propriedade ConnectionString na janela Propriedades. No entanto, se você limpar esse valor, a propriedade da cadeia de conexão do gerenciador de conexões do Excel não será mais válida até que Loop Foreach seja executado. Portanto você deve definir a propriedade DelayValidation como True nas tarefas ou no pacote em que o gerenciador de conexões é usado para evitar erros de validação. Você também deve usar o valor padrão de False para a propriedade RetainSameConnection do gerenciador de conexões do Excel. Se você alterar esse valor para True, cada iteração do loop continuará a abrir a primeira pasta de trabalho do Excel.

Selecione o novo gerenciador de conexões do Excel, clique na propriedade Expressões na janela Propriedades e clique nas reticências.

No Editor de Expressões de Propriedades, selecione a propriedade ConnectionString e depois clique no botão de reticências.

No Construtor de Expressões, digite a expressão a seguir:

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +  @[User::ExcelFile] + ";Extended Properties=\"" + @[User::ExtProperties] + "\""

Observe o uso do caractere de escape "\" para não ter que usar as aspas exigidas no valor do argumento Propriedades Estendidas.

O argumento Propriedades Estendidas não é opcional. Se você não usar uma variável para conter seu valor, adicione-a manualmente à expressão, como neste exemplo de um arquivo do Excel 2003:

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +  @[User::ExcelFile] + ";Extended Properties=Excel 8.0"

Crie tarefas no contêiner Loop Foreach que usam o gerenciador de conexões do Excel para executar as mesmas operações em cada pasta de trabalho do Excel que corresponde ao local e padrão de arquivo especificado.

Crie um gerenciador de conexões ADO.NET que use o provedor OLE DB do Microsoft Jet para se conectar a uma pasta de trabalho do Excel. Na página Tudo da caixa de diálogo Gerenciador de Conexões, insira o Excel 8.0 como o valor da propriedade Propriedades Estendidas. Para obter mais informações, consulte Como adicionar ou excluir um gerenciador de conexões em um pacote.

Crie uma variável de cadeia que receberá o nome da tabela atual em cada iteração do loop.

Adicione um contêiner Loop Foreach à guia Fluxo de Controle. Para obter informações sobre como configurar o contêiner Loop Foreach, consulte Como configurar um contêiner Loop Foreach.

Na página Coleção do Editor de Loop Foreach, selecione o Enumerador de Conjunto de Linhas de Esquema ADO.NET Foreach.

Como valor de Conexão, selecione o gerenciador de conexões ADO.NET que você criou anteriormente.

Como valor de Esquema, selecione Tabelas.

Observação
A lista de tabelas em uma pasta de trabalho do Excel inclui planilhas (que têm o sufixo $) e intervalos nomeados. Se você tiver que filtrar a lista para apenas planilhas ou apenas intervalos nomeados, talvez seja necessário gravar o código personalizado em uma tarefa Script para essa finalidade. Para obter mais informações, consulte Trabalhando com arquivos do Excel com a tarefa Script.

Na página Mapeamentos de Variáveis, mapeie Index 2 para a variável de cadeia criada anteriormente para manter o nome da tabela atual.

Feche o Editor de Loop Foreach.

Crie tarefas no contêiner Loop Foreach que usam o gerenciador de conexões do Excel para executar as mesmas operações em cada tabela do Excel na pasta de trabalho especificada. Se você usar uma tarefa Script para examinar o nome de tabela enumerado ou para trabalhar com tabelas individualmente, lembre-se de adicionar a variável de cadeia à propriedade ReadOnlyVariables da tarefa Script.