Переменные SSIS¶
Переменные SSIS — это именованные контейнеры для хранения значений в среде служб SQL Server Integration Services (SSIS), являющейся компонентом Microsoft SQL Server, предназначенным для создания пакетов извлечения, преобразования и загрузки данных (ETL). Переменные предоставляют механизм для хранения и передачи значений между задачами, контейнерами и обработчиками событий в рамках одного пакета SSIS, обеспечивая гибкость, динамическую настройку и управление логикой выполнения пакетов.
¶Типы переменных
SSIS поддерживает два основных типа переменных:
¶Системные переменные
Системные переменные создаются автоматически при создании пакета SSIS и содержат предопределённые значения, доступные только для чтения во время выполнения. Они предоставляют контекстную информацию о среде выполнения, такую как имя пакета, идентификатор пакета, имя компьютера, на котором выполняется пакет, и дата начала выполнения. Примеры системных переменных:
- System::PackageName — имя текущего пакета.
- System::StartTime — время начала выполнения пакета.
- System::UserName — имя пользователя, запустившего пакет.
- System::TaskName — имя текущей задачи или контейнера.
¶Пользовательские переменные
Пользовательские переменные создаются разработчиком и могут быть настроены по имени, типу данных, области видимости и начальному значению. Они могут быть как доступными для чтения, так и для записи, что позволяет изменять их значения в процессе выполнения пакета. Это основной инструмент для реализации динамической логики в пакетах ETL.
¶Область видимости переменных
Каждая переменная имеет определённую область видимости, которая определяет, где в пакете она может быть использована:
- Область видимости пакета — переменная доступна во всех задачах, контейнерах и обработчиках событий в пределах всего пакета.
- Область видимости контейнера — переменная доступна в рамках отдельного контейнера, такого как «Цикл по каждому элементу», «Цикл по условию» или «Последовательность», и во всех вложенных в него элементах.
- Область видимости обработчика событий — переменная доступна только в конкретном обработчике событий (например, OnError, OnPreExecute).
Область видимости выбирается при создании переменной и не может быть изменена после создания. Для доступа к переменной из другого контейнера или пакета используется механизм параметров или родительских переменных.
¶Типы данных
Переменные SSIS поддерживают большинство стандартных типов данных платформы .NET, включая:
- Boolean — логическое значение (True/False).
- Byte — 8-битное целое число без знака.
- Char — 16-битный символ Unicode.
- DateTime — дата и время.
- Decimal — 128-битное число с плавающей запятой.
- Double — 64-битное число с плавающей запятой.
- Int16, Int32, Int64 — целые числа со знаком.
- Object — ссылка на объект, позволяющая хранить любые, в том числе сложные, типы данных.
- String — строка символов Unicode.
При присвоении значения переменной автоматически происходит приведение типа, если это возможно. В случае несоответствия типов возникает ошибка на этапе проверки или выполнения пакета.
¶Использование переменных
¶В выражениях
Переменные могут использоваться в выражениях SSIS, которые применяются для динамической настройки свойств задач, контейнеров и соединений. Например, имя файла-источника данных может задаваться через выражение, содержащее значение переменной: "C:\\Data\\" + @[User::FileName]. Выражения записываются на языке выражений SSIS, который поддерживает функции для работы со строками, датами, математическими операциями и логикой.
¶В ограничениях с условиями (Precedence Constraints)
Переменные используются в ограничениях с условиями для управления потоком выполнения задач. Например, можно задать условие: «Выполнять следующую задачу только если значение переменной @[User::RowCount] больше 100». Это позволяет строить ветвление в зависимости от данных.
¶В задачах скрипта (Script Task)
Внутри задачи «Скрипт» переменные могут быть доступны для чтения и записи через объект Dts.Variables. Это позволяет реализовать сложную логику, которую невозможно выразить встроенными средствами SSIS, например, разбор нестандартных форматов данных или взаимодействие с внешними API.
¶В контейнерах «Цикл по каждому элементу» (Foreach Loop)
Контейнер «Цикл по каждому элементу» может перебирать элементы коллекции (например, файлы в папке, строки из БД) и присваивать значения переменным, которые затем используются внутри цикла для обработки каждого элемента.
¶Создание и управление
Переменные создаются в редакторе SSIS, который доступен в среде SQL Server Data Tools (SSDT) в составе Visual Studio. Для создания пользовательской переменной необходимо:
- Открыть окно «Переменные» (Variables window), нажав на соответствующую кнопку на панели инструментов или выбрав пункт меню SSIS → Variables.
- В окне создать новую переменную, указав её имя, тип данных и область видимости.
- При необходимости задать начальное значение.
Управление переменными включает редактирование их свойств, удаление, а также настройку чувствительности к регистру для имён.
¶Параметры vs Переменные
В более поздних версиях SSIS (начиная с SQL Server 2012) введен концепт параметров, которые частично заменили переменные для некоторых сценариев. Основные различия:
| Характеристика | Переменные | Параметры |
|---|---|---|
| Область видимости | Внутри пакета | Внутри пакета или проекта |
| Модификация во время выполнения | Да (пользовательские переменные) | Да (для параметров пакета) |
| Переопределение извне | Через конфигурации и команды | Автоматически при развертывании проекта |
| Управление чувствительностью к регистру | Есть (свойство Sensitive) | Есть (свойство Sensitive) |
Параметры рекомендуется использовать для значений, которые должны быть изменены при развертывании или выполнении пакета (например, пути к файлам, строки подключения), тогда как переменные остаются основным инструментом для внутренней логики пакета.
¶Примеры применения
- Динамическое имя файла: В задаче «Поток данных» имя файла-источника задаётся выражением, использующим переменную @[User::FileName], значение которой определяется на этапе выполнения из конфигурации или результата предыдущей задачи.
- Управление потоком: После выполнения задачи, загружающей данные из таблицы, проверяется количество строк. Если оно равно нулю, следующая задача (отправка уведомления) выполняется; если больше нуля — задача отправляет данные в хранилище.
- Логирование ошибок: Переменная @[User::ErrorCount] увеличивается на единицу внутри обработчика событий OnError. При выходе значения за предельно допустимое, выполнение пакета прерывается с помощью условия в ограничении.
¶Ограничения
- Переменные не могут быть использованы для хранения больших объёмов данных (например, целых таблиц); для этого служат объектные переменные с типом Object и сложные структуры, но это снижает производительность.
- Доступ к переменным из разных областей видимости требует явного указания родительского контейнера.
- В версиях SSIS до SQL Server 2005 использовался другой механизм управления переменными; переход на текущую версию требует миграции.
¶Источники
- Microsoft Learn. «Integration Services (SSIS) Variables» (документация).
- Knight, B., Veerman, E., & Robertson, D. (2012). Professional SQL Server 2012 Integration Services. Wrox.
- Онлайн-курсы и технические блоги сообщества SQL Server (SQLShack, MSSQLTips).