Переменные 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).

BFOmetr — база данных и аналитика по компаниям России.

На главную BFOmetr →