MS SQL Server 2012. Автоматизация экспорта данных в Excel

Доброго времени суток!

Хочу рассказать как автоматизировать экспорт данных в Excel из MS SQL Server 2012.

Сначала необходимо создать шаблон, т.е. обычный документ Excel формата xls. Для автоматизации экспорта данных следует создать пакет служб SSIS. Это можно сделать, используя SQL Server Business Intelligence или SQL Server Managment Studio. Я сделал экспорт, используя SQL Server Managment Studio.
 

Запустим SQL Server Managment Studio.
 

1) В обозревателе объектов выберем базу данных, которая содержит интересующую нас таблицу:

2) Нажмём правую кнопку мышки. В "Задачах" выберем "Экспортировать данные...":

3) Появится окно мастера импорта:

Жмём "Далее".

4) В следующем окне указываем "Источник данных", "Имя сервера" и интересующую нас базу данных:

Жмём "Далее".

 



5) Появится окно, которое будет содержать информацию о том, куда следует экспортировать данные. Т.к. экспорт будем делать в Excel, то в поле "Назначение" укажем "Microsoft Excel". Пропишем путь к файлу. У меня Excel 2013, поэтому версию Excel выбрал Microsoft Excel 97-2003, самую позднюю из доступных:
 

Жмём "Далее".

6) В следующем окне можем выбрать экспорт всех столбцов из таблицы или написать SQL-запрос и выбрать определённые столбцы.

В моём случае необходимо было указать определённые столбцы, поэтому я выбрал "Написать запрос, указывающий данные для передачи". Жмём "Далее".

 

7) В появившемся окне я написал простой запрос:

select CreatedOn as 'Дата создания',

          City as 'Город'

     from dbo.tbl_Test

В котором делаю выборку строк из столбцов "Дата создания" и "Город" таблицы dbo.tbl_Test.

 

Нажав на кнопку "Выполнить анализ" можно проверить запрос на ошибки. Жмём "Далее" 

 

8) В следующем окне в правом столбце вместо "Запрос" напишем "Тест". С таким названием лист будет отображаться в Excel.

 

Далее дважды кликнем по левому столбцу ([Запрос]). В появившемся окне видим, что для строки "Город" тип не определён:

Для этой строки указываем тип VarChar, размер 255. Для корректного отображения даты создания в отчёте необходимо для поля "Дата создания" указать тип DateTime. Если поставить маркер "Удалить и создать повторно целевую таблицу", это позволит многократно экспортировать данные в шаблон Excel , причём при каждом новом экспорте файл будет перезаписываться.

Жмём "Ок".

9) В следующем окне ничего не меняем, жмём "Далее".

10) Теперь можем сделать просто экспорт данных в шаблон Excel или сохранить пакет служб SSIS и, при необходимости, запускать его. Поставим маркер на "Сохранить пакет служб SSIS". Пакет можно сохранить на сервере или в файловой системе. Я сохранил в файловой системе. Жмём "Далее".

11) Выбрав папку, сохраним пакет как "Test.dtsx"

Жмём "Далее".

 

12) Появится окно:

Жмём "Готово".

 

13) Следующее окно говорит нам, что мы не допустили никаких ошибок.

Жмём "Закрыть". Если созданный пакет открыть в текстовом редакторе, то увидим, что в строке:

 DTS:ConnectionString="Provider= Microsoft.Jet.OLEDB.4.0;Data Source=C:\upload\aaa.xlsx;Extended Properties="Excel 8.0;HDR=YES";" />

в качестве поставщика указан Microsoft.Jet.OLEDB.4.0. Если у вас 64 разрядный сервер, то в качестве поставщика необходимо использовать Microsoft.ACE.OLEDB.15.0 или Microsoft.ACE.OLEDB.12.0. Если Excel позднее Excel 2007, то вместо Excel 8.0 следует указать Excel 12.0 (в этой же строке)

DTS:ConnectionString="Provider=Microsoft.ACE.OLEDB.15.0;Data Source=C:\upload\aaa.xlsx;Extended Properties="Excel 12.0;HDR=YES";" /> 

Запустив пакет:

 

EXEC xp_cmdshell 'dtexec /f "C:\upload\Test.dtsx"'

Go
 

и открыв файл excelTest.xls, получим:

Следует отметить, что дата отображается, как дата; числа (если они есть в отчёте), как числа; строки, как строки. Никакого дополнительного форматирования делать не надо. Стиль заголовков столбцов можно изменить. Повторный экспорт будет производиться с учетом сохранённого стиля.

Для автоматизации экспорта данных из таблицы в Excel необходимо создать job при помощи агента SQL сервера. Job будет запускать созданный пакет служб SSIS.