Просмотр файлов и таблиц Excel с помощью контейнера "Цикл по каждому элементу"
Область применения: 
среда выполнения интеграции SSIS SQL Server в Фабрика данных Azure
Процедуры в этом разделе описывают, как выполнить цикл по книгам Excel в папке или цикл по таблицам в книге Excel с помощью контейнера «цикл по каждому элементу» с соответствующим перечислителем.
Внимание
Дополнительные сведения о подключении к файлам Excel, а также об ограничениях и известных проблемах, связанных с загрузкой данных в файлы этого приложения и из них, см. в разделе Загрузка данных в приложение Excel или из него с помощью служб SQL Server Integration Services (SSIS).
Организация цикла по файлам Excel с помощью перечислителя с циклом по каждому файлу
Создайте строковую переменную, которая будет принимать значение текущего пути и имени файла Excel для каждой итерации цикла. Чтобы избежать проверки данных, присвойте переменной в качестве начального значения путь и имя существующего файла Excel. (Образец выражения, показанный ниже, использует в этой процедуре переменную
ExcelFile.)При желании создайте другую строковую переменную, которая будет хранить значение аргумента для дополнительных свойств строки соединения с Excel. Этот аргумент содержит серию значений, которые задают версию Excel и определяют, будет ли первая строка содержать имена столбцов и будет ли использован режим импорта. (Образец выражения, показанный ниже, использует в этой процедуре имя переменной
ExtPropertiesс начальным значением "Excel 12.0;HDR=Yes".)Если в аргументе расширенных свойств не используется переменная, то в выражение, которое содержит строку подключения, ее необходимо добавить вручную.
Добавьте контейнер цикла foreach на вкладку "Поток управления". Сведения о настройке контейнера цикла foreach см. в разделе "Настройка контейнера цикла foreach".
На странице Коллекция в редакторе циклов Foreach выберите перечислитель Foreach File, задайте папку, в которой размещаются книги Excel, и фильтр файлов (обычно *.xlsx).
На странице Сопоставления переменной сопоставьте Index 0 с определенной пользователем строковой переменной, которая будет принимать значение текущего пути Excel и имени файла на каждой итерации цикла. (Образец выражения, показанный ниже, использует в этой процедуре переменную
ExcelFile.)Закройте Редактор циклов по каждому элементу.
Добавьте диспетчер подключений к Excel в пакет, как описано в разделе Добавление, удаление или совместное использование диспетчера соединений в пакете. Чтобы избежать ошибок проверки, выберите для подключения существующий файл книги Excel.
Внимание
Чтобы избежать ошибок проверки после настройки задач и компонентов потоков данных, которые используют этот диспетчер подключений Excel, выберите существующую книгу Excel в окне Редакторе диспетчера соединений Excel. Диспетчер подключений не будет использовать эту книгу во время выполнения, если настроить выражение для свойства ConnectionString , как показано ниже. После создания и настройки пакета можно очистить значение свойства ConnectionString в окне свойств. Однако если очистить это значение, свойство строки соединения диспетчера соединений Excel не будет действительно до запуска контейнера «цикл по каждому элементу». Следовательно, необходимо присвоить свойству DelayValidation значение True в задачах, где используется диспетчер подключений, или в пакете, чтобы избежать ошибок проверки.
Необходимо также использовать значение по умолчанию False для свойства RetainSameConnection диспетчера подключений Excel. При изменении этого значения на Trueкаждая итерация цикла будет по-прежнему открывать первую книгу Excel.
Выберите новый диспетчер подключений Excel, щелкните свойство Выражения в окне свойств и нажмите кнопку с многоточием.
В редакторе выражений свойстввыберите свойство ConnectionString и нажмите кнопку с многоточием.
В построителе выражения введите следующее выражение.
"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"Создайте задачи в контейнере «цикл по каждому элементу», которые используют диспетчер соединений с Excel для выполнения одинаковых операций для каждой книги Excel, соответствующей заданному положению и шаблону файла.
Организация цикла по таблицам Excel с помощью перечислителя по набору строк схемы ADO.NET
Создайте диспетчер подключений ADO.NET, который использует OLE DB-поставщик Microsoft ACE для подключения к книге Excel. В диалоговом окне Диспетчер подключений на странице "Все" убедитесь, что в качестве значения расширенных свойств введена версия Excel (в данном случае Excel 12.0). Дополнительные сведения см. в статье Добавление, удаление или совместное использование диспетчера соединений в пакете.
Создайте строковую переменную, которая будет принимать имя текущий таблицы на каждой итерации цикла.
Добавьте контейнер цикла foreach на вкладку "Поток управления". Сведения о настройке контейнера цикла foreach см. в разделе "Настройка контейнера цикла foreach".
На странице Коллекция в редакторе циклов по каждому элементувыберите перечислитель набора строк схемы ADO.NET.
В качестве значения Соединениевыберите предварительно созданный диспетчер подключений ADO.NET.
В качестве значения Схемавыберите "Таблицы".
Примечание.
Список таблиц в книге Excel включает в себя и листы (которые имеют суффикс $), и именованные диапазоны. Если нужно отфильтровать список только по листам или только по именованным диапазонам, то, возможно, для этой цели понадобится написать свою программу в задаче «Скрипт». Дополнительные сведения см. в статье Работа с файлами Excel в задаче "Скрипт".
На странице Сопоставления переменной сопоставьте Index 2 со строковой переменной, созданной ранее для хранения имени текущей таблицы.
Закройте Редактор циклов по каждому элементу.
Создайте задачи в контейнере «цикл по каждому элементу», которые используют диспетчер соединений Excel для выполнения одинаковых операций для каждой таблицы Excel в заданной книге. Если задача "Скрипт" используется для анализа имени перечисляемой таблицы или работы с каждой таблицей, не забудьте добавить строковую переменную к свойству ReadOnlyVariables задачи "Скрипт".
См. также
Загрузка данных в приложение Excel или из него с помощью служб SQL Server Integration Services (SSIS)
Настройка контейнера «цикл по каждому элементу»
Добавление или изменение выражение свойства
Диспетчер подключений Excel
Источник Excel
Назначение «Excel»
Работа с файлами Excel в задаче «Скрипт»