Просмотр файлов и таблиц Excel с помощью контейнера "Цикл по каждому элементу"

Применимо к: SQL Server Integration Runtime служб SSIS в Фабрика данных Azure

Процедуры в этом разделе описывают, как выполнить цикл по книгам Excel в папке или цикл по таблицам в книге Excel с помощью контейнера «цикл по каждому элементу» с соответствующим перечислителем.

Важно!

Дополнительные сведения о подключении к файлам Excel, а также об ограничениях и известных проблемах, связанных с загрузкой данных в файлы этого приложения и из них, см. в разделе Загрузка данных в приложение Excel или из него с помощью служб SQL Server Integration Services (SSIS).

Организация цикла по файлам Excel с помощью перечислителя с циклом по каждому файлу

1. Создайте строковую переменную, которая будет принимать значение текущего пути и имени файла Excel для каждой итерации цикла. Чтобы избежать проверки данных, присвойте переменной в качестве начального значения путь и имя существующего файла Excel. (Образец выражения, показанный ниже, использует в этой процедуре переменную ExcelFile.)

2. При желании создайте другую строковую переменную, которая будет хранить значение аргумента для дополнительных свойств строки соединения с Excel. Этот аргумент содержит серию значений, которые задают версию Excel и определяют, будет ли первая строка содержать имена столбцов и будет ли использован режим импорта. (Образец выражения, показанный ниже, использует в этой процедуре имя переменной ExtPropertiesс начальным значением "Excel 12.0;HDR=Yes".)

   Если в аргументе расширенных свойств не используется переменная, то в выражение, которое содержит строку подключения, ее необходимо добавить вручную.

3. Добавьте контейнер "цикл по каждому элементу" на вкладку Поток управления . Сведения о настройке контейнера "цикл по каждому элементу" см. в разделе Настройка контейнера "цикл по каждому элементу".

4. На странице Коллекция в редакторе циклов Foreach выберите перечислитель Foreach File, задайте папку, в которой размещаются книги Excel, и фильтр файлов (обычно *.xlsx).

5. На странице Сопоставления переменной сопоставьте Index 0 с определенной пользователем строковой переменной, которая будет принимать значение текущего пути Excel и имени файла на каждой итерации цикла. (Образец выражения, показанный ниже, использует в этой процедуре переменную ExcelFile.)

6. Закройте Редактор циклов по каждому элементу.

7. Добавьте диспетчер подключений к Excel в пакет, как описано в разделе Добавление, удаление или совместное использование диспетчера соединений в пакете. Чтобы избежать ошибок проверки, выберите для подключения существующий файл книги Excel.

   Важно!

   Чтобы избежать ошибок проверки после настройки задач и компонентов потоков данных, которые используют этот диспетчер подключений Excel, выберите существующую книгу Excel в окне Редакторе диспетчера соединений Excel. Диспетчер подключений не будет использовать эту книгу во время выполнения, если настроить выражение для свойства ConnectionString , как показано ниже. После создания и настройки пакета можно очистить значение свойства ConnectionString в окне свойств. Однако если очистить это значение, свойство строки соединения диспетчера соединений Excel не будет действительно до запуска контейнера «цикл по каждому элементу». Следовательно, необходимо присвоить свойству DelayValidation значение True в задачах, где используется диспетчер подключений, или в пакете, чтобы избежать ошибок проверки.

   Необходимо также использовать значение по умолчанию False для свойства RetainSameConnection диспетчера подключений Excel. При изменении этого значения на Trueкаждая итерация цикла будет по-прежнему открывать первую книгу Excel.

8. Выберите новый диспетчер подключений Excel, щелкните свойство Выражения в окне свойств и нажмите кнопку с многоточием.

9. В редакторе выражений свойстввыберите свойство ConnectionString и нажмите кнопку с многоточием.

10. В построителе выражения введите следующее выражение.

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

    Обратите внимание на использование escape-символа «\» для обозначения внутреннего знака вопроса, требуемого около значения аргумента расширенных свойств.

    Аргумент расширенных свойств является обязательным. Если для этого значения не используется переменная, то его необходимо вручную добавить в выражение, как показано в следующем примере:

    "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +  @[User::ExcelFile] + ";Extended Properties=Excel 12.0"  
    

11. Создайте задачи в контейнере «цикл по каждому элементу», которые используют диспетчер соединений с Excel для выполнения одинаковых операций для каждой книги Excel, соответствующей заданному положению и шаблону файла.

Организация цикла по таблицам Excel с помощью перечислителя по набору строк схемы ADO.NET

1. Создайте диспетчер подключений ADO.NET, который использует OLE DB-поставщик Microsoft ACE для подключения к книге Excel. В диалоговом окне Диспетчер подключений на странице "Все" убедитесь, что в качестве значения расширенных свойств введена версия Excel (в данном случае Excel 12.0). Дополнительные сведения см. в статье Добавление, удаление или совместное использование диспетчера соединений в пакете.

2. Создайте строковую переменную, которая будет принимать имя текущий таблицы на каждой итерации цикла.

3. Добавьте контейнер "цикл по каждому элементу" на вкладку Поток управления . Сведения о настройке контейнера "цикл по каждому элементу" см. в разделе Настройка контейнера "цикл по каждому элементу".

4. На странице Коллекция в редакторе циклов по каждому элементувыберите перечислитель набора строк схемы ADO.NET.

5. В качестве значения Соединениевыберите предварительно созданный диспетчер подключений ADO.NET.

6. В качестве значения Схемавыберите "Таблицы".

   Примечание

   Список таблиц в книге Excel включает в себя и листы (которые имеют суффикс $), и именованные диапазоны. Если нужно отфильтровать список только по листам или только по именованным диапазонам, то, возможно, для этой цели понадобится написать свою программу в задаче «Скрипт». Дополнительные сведения см. в статье Работа с файлами Excel в задаче "Скрипт".

7. На странице Сопоставления переменной сопоставьте Index 2 со строковой переменной, созданной ранее для хранения имени текущей таблицы.

8. Закройте Редактор циклов по каждому элементу.

9. Создайте задачи в контейнере «цикл по каждому элементу», которые используют диспетчер соединений Excel для выполнения одинаковых операций для каждой таблицы Excel в заданной книге. Если задача "Скрипт" используется для анализа имени перечисляемой таблицы или работы с каждой таблицей, не забудьте добавить строковую переменную к свойству ReadOnlyVariables задачи "Скрипт".

См. также

Загрузка данных в приложение Excel или из него с помощью служб SQL Server Integration Services (SSIS)
Настройка контейнера «цикл по каждому элементу»
Добавление или изменение выражение свойства
Диспетчер подключений Excel
Источник Excel
Назначение «Excel»
Работа с файлами Excel в задаче «Скрипт»