Можно ли хранить данные, строить по ним отчетность, при этом обходясь без ETL процессов? Технически — да. Практически — только до первого серьезного роста данных.

Привет, Хабр! В этой статье мы расскажем о критически важном этапе, через который проходит любая data-driven компания, и через который прошли и мы.

Речь о переходе:
от построения отчетности напрямую из операционных баз (или через примитивное копирование в STG) к структурированным ETL-процессам на специализированном ПО.

В нашем случае этим ПО стал SQL Server Integration Services (SSIS) — платформа от Microsoft для создания решений по интеграции данных. Она позволяет извлекать, преобразовывать и загружать данные (ETL) из различных источников.

Но важно подчеркнуть: сейчас мы используем NiFi с [N] процессорами для управления data pipeline. Однако именно опыт с SSIS стал для нас тем самым «мостиком» между хаотичным и осознанным подходом к данным.

P.S. Если хотите узнать про то, как мы организовали работу в NiFi — пишите в комментах, сделаем отдельный материал!

В этой статье — только про этап с SSIS. Не потому что он «лучший», а потому что:

Это неизбежная ступень для команд внутри MS-стека
На его ошибках учатся вне зависимости от выбора инструмента
70% проблем были не в SSIS, а в наших процессах

Как все было до SSIS: темные времена «ручного» ETL

В 2015 году мы запустили систему подачи заявок на финансирования. Админкой системы служила «1С Битрикс: управление сайтом», а базой данных — MySQL. В то же время отчетность строилась с помощью SSRS из базы данных MS SQL Server, данные в которую попадали весьма незамысловатым образом, который сложно назвать ETL-процессом.

Архитектура «каменного века»:

SQL Agent Job запускал одну гигантскую хранимую процедуру
Процедура через OPENQUERY тянула «живые» данные из MySQL-монолита
Данные мержились прямо в таблицы хранилища для отчетности
Как выглядел наш процесс работы с данными без ETL

Проблемы такого подхода:

❌ Скорость – обновление даже небольшого набора таблиц занимало десятки минут

❌ Нагрузка – прямые запросы к MySQL под нагрузкой вызывали лаги в основном сервисе

❌ Хрупкость – падение одной таблицы «убивало» весь процесс обновления

❌ Типы данных – постоянные конфликты форматов (например, NTEXT → NVARCHAR)

Когда источников данных стало больше, а требования к стабильности выросли, мы решили перейти на SSIS.

SSIS: как мы построили ETL

Сразу обозначу, что выбор пал на SQL Server Integration Services по двум причинам:

Интеграция с уже используемым стеком Microsoft (SQL Server, SSMS).
Визуальный конструктор вместо тонн SQL-кода.

В SSIS получилось реализовать стандартный ETL процесс, где данные из монолита загружались в промежуточный слой, а уже из staging-слоя пробрасывались в таблицы озера данных, на основании которых и строилась отчетность.

Как стал выглядеть выстроенный ETL-процесс с помощью SSIS

Итак, как выглядит каждый шаг построенного нами ETL процесса.

Источники данных
- MySQL, PostgreSQL, MSSQL
- Excel/CSV-файлы
- API (REST)
- Парсинг веб-страниц.
Staging-слой (STG)
В промежуточном слое проводилась минимальная обработка.
- Данные в «сыром» виде (максимально близко к источнику)
- Только критичные преобразования:
  - Сохранение оригинальных имен полей и имен таблиц
  - Фильтрация активных записей
  - Исправление проблемных типов данных (например, NTEXT → NVARCHAR).
Промежуточные слои создавались один к одному с источниками данных: система приема заявок на получение займа — один слой, сопровождение займов — другой слой. По мере дробление монолитных приложений на сервисы, росло и число промежуточных слоев.
Озеро данных (DWH)
- Оптимизированные таблицы для отчетности
- Справочники (dim-таблицы)
- Агрегаты и предрасчеты.
Таким образом, в озеро уже поступают полностью готовые и обработанные данные. Для построения отчетности необходимо только сделать JOIN’ы таблиц.
Запуск
Остался привычный механизм для обновления таблиц:
SQL Agent Jobs по настроенному расписанию запускает цепочку хранимых процедур с вызовом SSIS-пакетов (1 шаг — 1 процедура — 1 таблица).

Т.е. через JOB вызывалась хранимая процедура в соответствующем слое с припиской “etl”, в которой:

запускался SSIS пакет
вызывалась хранимая процедура с припиской “stg”.
В этой процедуре происходило преобразование данных к нужному виду, MERGE и обновление таблиц хранилища, чтобы в верхнем слое получить данные для отёчности.

SSIS в Visual Studio: настройка и примеры

Для того, чтобы создавать SSIS пакеты в VS необходимо установить ряд дополнений.

SQL Server Data Tools (SSDT) – компонент для VS
Драйверы для подключения к источникам:
- MySQL: Connector/NET.
- PostgreSQL: Npgsql.
  Для Npgsql требуется дополнительная настройка Connection Manager в SSIS (выбор .NET Providers/Npgsql).
Настройка ODBC (для файловых источников) – для Windows через ODBC драйвер настроить администрирование источников данных с указанием сервера, пароля, порта
- настроить коннектор и параметры подключение
  Настроенные коннекторы к нашим источникам данных

Приведем конкретный пример создания SSIS-пакета, отвечающего за проброс данных операционной базы данных MySQL в промежуточный слой.

Сам пакет состоит из трех составляющих:

TRUNCATE таблицы промежуточного слоя
Проброс данных в Stg слой
Комментарий: дата, автор, краткое описание функции пакета
Пример готового SSIS пакета. Поток управления

Обратите внимание на настроенные подключения в «Диспетчеры подключений», в этом блоке должны отображаться коннекты с необходимыми БД (источник, хранилище и др. необходимые).

Во вкладке «Поток данных» при переходе в блок проброса, процесс устроен таким образом:

Делается селект из продовой БД MySQL.
Данные селекта вставляются в промежуточный слой.

В настройках блока SELECTиз списка доступных подключений выбирается источник данных, прописывается запрос на языке SQL, и нужные столбцы для проброса в Stg слой.

В настройках блока INSERTвыбирается нужное соединение, по которому доступен Stg слой, а также указывается название Stg таблицы, куда будут пробрасываться данные. После этого делается сопоставление полей таблицы-источника и таблицы-назначения.

В данном примере INSERT_DATE определяется процедурой, как текущее время на момент проброса, поэтому в первой ячейке Входного столбца пусто.

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

разграничение на слои
стабильная загрузка данных
распознавание типов данных (на основании метаданных в пакете проброса осуществляется переход от специфичный типов исходных данных к более стандартным типам, используемым в MSSQL)
графический подход, который наглядно иллюстрирует поток данных.

Неочевидные применения SSIS

После того, как был выстроен и отлажен ETL процесс, мы нашли еще несколько применений SSIS пакетам. Итак, вот что еще мы делали с их помощью.

Управление сервисами
- Перезапуск служб Windows
- Остановка/запуск процессов
- Рассылка email-уведомлений при ошибках.
Тестовые окружения
- Автоматический бэкап продовой БД → развертывание в тестовый контур.
Обратный поток данных
- Обновление отдельных сущностей в продуктовых БД из данных DWH.
Более тонкая настройка MERGE в отдельных случаях, когда нужно обновлять не всю большую таблицу целиком, а лишь небольшой фрагмент изменяющихся данных.

Ошибки и как их избежать

Уведомления

При откладке всех пакетов мы настраивали уведомления в JOB’е (который и запускал процедуры с SSIS пакетами), и нам на почту приходило сообщение о тех шагах JOB’а, где произошла ошибка и текст самой ошибки.

В таком случае мы действовали по следующему алгоритму:

Открывали процедуру шага JOB’а, где произошла ошибка
Анализировали, в какой момент произошел обвал: в пакете при запуске или же в процедуре при MERGE таблиц промежуточного и верхнего слоя.

Деплой

Было и такое, что локально пакет запускался без ошибок, а при разворачивании на сервере, валился с ошибкой.

В таком случае мы решали проблему следующим образом:

переходили на сервер, куда деплоются пакеты (Integrationn Services Catalog),
проваливались в пакет и делали Execute с сервера.

Это помогало проанализировать возможность запуска пакета именно с сервера.

Документация: описание реализованной структуры

Документации по созданным пакетам у нас не существовало. Без нее нам приходилось нелегко, т.к. в какой-то момент источников, пробросов, и, соответственно, пакетов стало очень много, и приходилось залезать в каждый, чтобы вспомнить, с чем именно он работает, какие источники включает, куда пробрасывает данные.

Для того, чтобы быстро разобраться, мы использовали:

понятные нейминг источников, процедур, таблиц Stg слоя, самих SSIS пакетов, JOB’ов
комментарии в каждом из пакетов

Поэтому, исходя из нашего опыта мы составили чек-лист для тех, кто решит пойти по нашему пути в мир SSIS Microsoft.

Наш чек-лист для стабильной работы пакетов SSIS

Логирование
- Включить SSISDB (каталог Integration Services)
Деплой
- Тестировать запуск с сервера через Execute Package
- Версионировать пакеты (например, через Git)
Мониторинг
- SQL Agent → настройка Notify Operator при ошибках: название процедуры, пакета и текст самой ошибки
- Дополнительные проверки в хранимых процедурах
Документация
- структуры ETL-процесса в целом и каждого SSIS пакета в отдельности
Наличие единого стиля разработки пакетов, что вкупе с документацией позволит минимизировать отклонения от выверенного и рабочего шаблона.

Что бы мы сделали иначе сегодня

Настроили бы более подробное логгирование и уведомления об ошибках в SSIS пакетах.
Сделали бы бота, который уведомляет о переполнении логгов, указывает, на какой машине произошло это.
Внедрили бы систему контроля версий, чтобы обезопасить от обвала и потери актуальной версии пакетов.
Добавили бы настройку зеркалирования продуктовых БД, чтобы сам ETL-процесс обращался не в прод за первичными данными, а в отзеркаленную БД, которая полностью повторяет прод. Это абсолютно нивелирует риски избыточной нагрузки прода.
И, конечно, документация! Написали бы подробную документацию о том, как и что храниться, откуда и куда пробрасывается, с помощью какого пакета.

Вывод

SSIS — отличный промышленный инструмент для выстраивания ETL процессов, особенно, если:

У вас уже есть стек Microsoft
Нужен визуальный конструктор без глубокого погружения в код
Требуется поддержка «экзотических» источников данных

ETL — это не только про технологии, но и про дисциплину. Даже с SSIS можно создать хаос, если нет версионирования, логов и документации.

А как у вас организован ETL? Делитесь в комментариях!

SSIS в Visual Studio: как мы перешли от хаоса к стабильному ETL-процессу