Время прочтения: 5 мин.

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

Допустим, имеется 5 Excel файлов, которые необходимо импортировать в целевую таблицу MS SQL Server. В каждом из этих файлов имеется по 1 листу с информацией следующего вида:

С помощью данного скрипта создадим целевую таблицу для импорта:

CREATE TABLE [dbo].[SSISExcelImport]
(
	[ID клиента] int,
	[ID офиса] int,
	[Наименование файла] nvarchar(20)

)

После создания целевой таблицы, откроем Visual Studio и создадим новый проект SSIS.  Далее необходимо создать и настроить подключения к источникам. На вкладке «Control Flow» правой кнопкой мыши выберем создать «New Connection»

Выберем пункт Excel в списке «Connection manager type» в качестве входного источника данных.

Для создания подключения на вкладке «Excel Connection Manager» укажем любой из имеющихся файлов, которые требуется импортировать. После указания пути к файлу будет автоматически выбрана используемая версия Microsoft Excel.

После нажатия на кнопку «ОК» настроенное соединение будет доступно в области «Connection Managers»

Далее, в списке «Connection manager type» необходимо выбрать источник назначения «OLE DB»

В «OLE DB connection manager» необходимо указать свойства подключения к экземпляру СУБД MS SQL Server, после чего в области «Connection Managers» будут доступны настроенные соединения к входному и целевому источникам. Настроенные соединения мы будем использовать в задачах потока и управления данных разрабатываемого SSIS пакета.

Добавим элемент «Data Flow Task» для импорта данных из Excel в таблицу MS SQL Server.

Дважды щелкнув по данному элементу, мы переместимся на вкладку «Data Flow», и добавим элемент «Excel Source».

Дважды щелкнув по источнику Excel в «Excel Source Editor», выберем лист с информацией для импорта. Листы с данными должны иметь аналогичную структуру по всем Excel файлам.

При нажатии на кнопку «Preview» имеется возможность посмотреть образцы данных в загружаемых файлах Excel.

Добавим элемент «OLE DB Destination» и соединим с элементом «Excel Source»:

Через двойной щелчок по элементу «OLE DB Destination» будет открыт редактор данного элемента, в котором необходимо указать соединение «OLE DB» и целевую таблицу, которые были созданы ранее.

Добавим переменные для определения Excel файлов, которые будут обрабатываться в цикле FOR EACH.

  • Directory: путь к директории с Excel файлами;
  • Path: путь к текущему Excel файлу. В данную переменную будут подставляться пути к файлам папки во время перебора.

Выберем «Excel Connection Manager» и отобразим свойства, и далее выберем «Expressions»

После нажатия кнопки «OK», данная переменная будет доступна во вкладке «Property Expressions Editor».

В результате данных действий появится соответствующий символ «fx» в пункте «Excel Connection Manager» вкладки «Connection Managers»

Далее необходимо настроить контейнер «Foreach Loop Container» для импорта данных из Excel файлов.

Для этого необходимо дважды щелкнуть по элементу «Foreach Loop Container», откроется соответствующий редактор, на вкладке «Collection» требуется указать реквизиты с директорией Excel файлов и маску файлов, которые будут импортированы.

Таким образом реализован SSIS пакет для импорта большого количества Excel файлов.

Запустим пакет на исполнение.

Убедимся, что данные загружены в целевую таблицу MS SQL Server, и сверим с одним из Excel файлов, например Excel_Source_3

Таким образом мы проверили, что данные в целевой таблице и исходных файлах имеют аналогичную структуру и набор данных.

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