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

Можно ли хранить данные, строить по ним отчетность, при этом обходясь без 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-процессом.

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

1. SQL Agent Job запускал одну гигантскую хранимую процедуру

2. Процедура через OPENQUERY тянула «живые» данные из MySQL-монолита

3. Данные мержились прямо в таблицы хранилища для отчетности

   

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

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

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

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

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

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

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

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

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

2. Визуальный конструктор вместо тонн SQL-кода.

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

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

1.Источники данных

• MySQL, PostgreSQL, MSSQL

• Excel/CSV-файлы

• API (REST)

• Парсинг веб-страниц

2.Staging-слой (STG)

В промежуточном слое проводилась минимальная обработка.

• Данные в «сыром» виде (максимально близко к источнику)

• Только критичные преобразования:

  • Сохранение оригинальных имен полей и имен таблиц

  • Фильтрация активных записей

  • Исправление проблемных типов данных (например, NTEXT → NVARCHAR)

Промежуточные слои создавались один к одному с источниками данных: система приема заявок на получение займа — один слой, сопровождение займов — другой слой. По мере дробление монолитных приложений на сервисы, росло и число промежуточных слоев.

3. Озеро данных (DWH)

• Оптимизированные таблицы для отчетности

• Справочники (dim-таблицы)

• Агрегаты и предрасчеты

Таким образом, в озеро уже поступают полностью готовые и обработанные данные. Для построения отчетности необходимо только сделать JOIN’ы таблиц.

4. Запуск

Остался привычный механизм для обновления таблиц:
SQL Agent Jobs по настроенному расписанию запускает цепочку хранимых процедур с вызовом SSIS-пакетов (1 шаг — 1 процедура — 1 таблица).

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

• запускался SSIS пакет

• вызывалась хранимая процедура с припиской “stg”.

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

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

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

1. SQL Server Data Tools (SSDT) – компонент для VS

2. Драйверы для подключения к источникам:

   • MySQL: Connector/NET.

   • PostgreSQL: Npgsql.
     Для Npgsql требуется дополнительная настройка Connection Manager в SSIS (выбор .NET Providers/Npgsql).

3. Настройка ODBC (для файловых источников) – для Windows через ODBC драйвер настроить администрирование источников данных с указанием сервера, пароля, порта

   • настроить коннектор и параметры подключение

     

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

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

1. TRUNCATE таблицы промежуточного слоя

2. Проброс данных в Stg слой

3. Комментарий: дата, автор, краткое описание функции пакета

   

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

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

1. Делается селект из продовой БД MySQL.

2. Данные селекта вставляются в промежуточный слой.

   

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

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

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

• разграничение на слои

• стабильная загрузка данных

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

• графический подход, который наглядно иллюстрирует поток данных

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

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

1. Управление сервисами

   • Перезапуск служб Windows

   • Остановка/запуск процессов

   • Рассылка email-уведомлений при ошибках

2. Тестовые окружения

   • Автоматический бэкап продовой БД → развертывание в тестовый контур

3. Обратный поток данных

   • Обновление отдельных сущностей в продуктовых БД из данных DWH.

4. Более тонкая настройка MERGE в отдельных случаях, когда нужно обновлять не всю большую таблицу целиком, а лишь небольшой фрагмент изменяющихся данных.

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

Уведомления

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

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

1. Открывали процедуру шага JOB’а, где произошла ошибка

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

Деплой

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

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

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

• проваливались в пакет и делали Execute с сервера.

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

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

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

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

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

• комментарии в каждом из пакетов

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

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

1. Логирование

   • Включить SSISDB (каталог Integration Services)

2. Деплой

   • Тестировать запуск с сервера через Execute Package

   • Версионировать пакеты (например, через Git)

3. Мониторинг

   • SQL Agent → настройка Notify Operator при ошибках: название процедуры, пакета и текст самой ошибки

   • Дополнительные проверки в хранимых процедурах

4. Документация

   • структуры ETL-процесса в целом и каждого SSIS пакета в отдельности

5. Наличие единого стиля разработки пакетов, что вкупе с документацией позволит минимизировать отклонения от выверенного и рабочего шаблона.

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

1. Настроили бы более подробное логгирование и уведомления об ошибках в SSIS пакетах. Сделали бы бота, который уведомляет о переполнении логгов, указывает, на какой машине произошло это.

2. Внедрили бы систему контроля версий, чтобы обезопасить от обвала и потери актуальной версии пакетов.

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

4. И, конечно, документация! Написали бы подробную документацию о том, как и что храниться, откуда и куда пробрасывается, с помощью какого пакета.

Вывод

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

• У вас уже есть стек Microsoft

• Нужен визуальный конструктор без глубокого погружения в код

• Требуется поддержка «экзотических» источников данных

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

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