Динамическое создание файлов Excel в пакете MS Integration Services (SSIS)

Задача: В процессе выполнения SSIS пакета формировать файлы Excel с информацией. Например вы выгружаете информацию по продажам и необходимо формировать отдельный файл для каждого филиала.

Считаем, что вы знаете как пользоваться Foreach Loop контейнером, т.к. создание Excel файлов находится внутри него и у вас настроены все источники данных.

1. Добавить Execute SQL Task в контейнер цикла
2. В его свойствах указать ConnectionType = EXCEL
3. Создать новое подключение, раскрыв список в Connection

4. Указать путь к файлу. Важно: можно как выбрать существующий файл, так и указать имя отсутствующего файла, но по существующему пути. В моем случае путь E:\SSIS_Packages\Temp\ реальный, а файл Temp.xls не существует.

5. Указать версию Excel

6. Сохраняем все настройки (элемент будет отображен с ошибкой, пока игнорируем)

7. Добавляем Data Flow Task

8. Соединяем Execute SQL Task с Data Flow Task

9. Внутрь Data Flow Task добавляем ваш источник данных (не забываем, что Excel принимает unicode формат, поэтому необходимо сделать предварительную конвертацию), Excel Destination и соединяем их

10. Создаем переменную sqlCreateExcelSheet, которая будет содержать скрипт создания листа Excel

11. Создаем переменную strExcelListName, которая будет содержать название листа в Excel

12. Открываем свойства Excel Destination, выбираем ранее созданный Connection Manager, способ доступа к данным Table or view и нажимаем кнопку New...

13. В новом окне откроется скрипт создания листа Excel, в нем меняем все varchar и nvarchar на LongText, а также меняем имя листа на свое, например MySheet. Копируем скрипт и сохраняем.

14. В свойстве Name of the Excel sheet выбираем лист со знаком $, который мы только что создали и переходим в раздел Mappings, в котором настраиваем все соответствия источника и приемника. Сохраняем.

15. В переменную strExcelListName сохраняем имя нашего листа без знака $. В нашем случае это MySheet

16. Открываем для переменной sqlCreateExcelSheet окно Expression и вставляем в скрипт, скопированный на шаге 13. Заменяем название листа в тексте скрипта на переменную strExcelListName и сохраняем

17. Возвращаемся в свойства Execute SQL Task. SQLSourceType = Variable, SourceVariable = User::sqlCreateExcelSheet. Сохраняем.

18, В свойствах Exce Connection Manager ставим DelayValidation = true, т.к.файлы будут существовать только на момент выполнения пакета

19. Также у вас должна существовать переменная strFileName, которая содержит имя и путь файла, мы предполагаем, что в каждой итерации цикла в ней будет свое значение (не описано в рамках этой статьи). Открываем раздел Expressions Excel Connection Manager и переопределяем свойство ExcelFilePath на @[User::strFileName]

20. Финальным шагом в свойствах Excel Destination указываем способ получения данных Table name or view name variable и в качестве переменной выбираем strExcelListName

21. Запускаем пакет.