Открыть сервис
Главная → Блог → Пользовательские переменные SSIS

Пользовательские переменные SSIS

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

Типы переменных

В SSIS существует два основных типа переменных: системные и пользовательские. Пользовательские переменные создаются разработчиком и могут быть настроены под конкретные нужды пакета.

Системные переменные

Системные переменные автоматически создаются средой SSIS для каждого пакета, контейнера или задачи. Они предоставляют информацию о состоянии выполнения, такую как имя пакета (System::PackageName), время запуска (System::StartTime), количество обработанных строк (System::RowCount) и имя компьютера (System::MachineName). Системные переменные доступны только для чтения в большинстве контекстов.

Пользовательские переменные

Пользовательские переменные создаются вручную в редакторе SSIS (среда Business Intelligence Development Studio или SQL Server Data Tools). Они могут быть объявлены на уровне пакета, контейнера (например, цикл ForEach или последовательность) или задачи. Каждая переменная имеет:

  • Имя — уникальный идентификатор в рамках своей области видимости.
  • Тип данных — один из типов, поддерживаемых SSIS (Boolean, Int32, String, DateTime, Object и др.).
  • Значение — начальное значение, которое может быть задано статически или вычислено выражением.
  • Область видимости — определяет, в каких частях пакета переменная доступна (пакет, контейнер, задача).

Создание и настройка

Пользовательские переменные создаются через окно «Переменные» (Variables) в среде разработки SSIS. Для этого:

  1. Открывается пакет SSIS в конструкторе.
  2. Выбирается вкладка «Переменные» (или используется меню «SSIS» → «Переменные»).
  3. Нажимается кнопка «Добавить переменную».
  4. Задаётся имя, тип данных, значение и область видимости.

Переменные могут быть также созданы программно с помощью объектной модели SSIS (например, в скриптовой задаче). Для переменных можно задать выражение, которое вычисляется динамически при каждом обращении к переменной. Это позволяет, например, формировать текущую дату или путь к файлу на основе параметров.

Область видимости

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

Использование в задачах и контейнерах

Пользовательские переменные широко применяются в различных компонентах SSIS:

В задачах потока управления

  • Задача «Выполнение SQL» — переменные могут использоваться для передачи параметров в SQL-запрос (например, SELECT * FROM Table WHERE Date = ?).
  • Задача «Скрипт» — переменные передаются в скриптовую задачу для чтения и записи данных.
  • Задача «Выражение» — переменные могут быть присвоены свойствам задач (например, путь к файлу для задачи «Файловая система»).

В контейнерах

  • Цикл «For» — переменная используется как счётчик итераций.
  • Цикл «ForEach» — переменная может хранить текущий элемент коллекции (например, имя файла из папки).
  • Последовательность — переменные внутри последовательности изолированы от внешнего контекста.

В потоке данных

  • Компонент «Производный столбец» — переменные могут быть использованы для вычисления новых столбцов на основе значений переменных.
  • Компонент «Условное разбиение» — переменные могут участвовать в условиях маршрутизации строк.
  • Компонент «OLE DB Источник» — переменные могут передавать параметры в SQL-запрос источника данных.

Передача значений между пакетами

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

  • В родительском пакете создаётся задача «Выполнение пакета», которая запускает дочерний пакет.
  • В дочернем пакете создаются переменные, которые помечены как «Параметры» (в SSIS 2012 и новее) или настраиваются через конфигурации пакета.
  • Значения передаются через механизм параметров или через родительские переменные.

Выражения и динамические значения

Переменные могут быть связаны с выражениями, которые вычисляются во время выполнения. Например, переменная CurrentDate может иметь выражение GETDATE(), и при каждом обращении к ней будет возвращаться текущая дата. Выражения поддерживают функции SSIS (математические, строковые, даты) и могут ссылаться на другие переменные.

Отладка и мониторинг

В среде разработки SSIS значения переменных можно просматривать в окне «Локальные» (Locals) при отладке пакета. Также можно добавить точки останова на изменение значения переменной. В продуктивной среде значения переменных могут быть записаны в журнал выполнения (логи) через настройки ведения журнала SSIS.

Примеры применения

  • Параметризация путей к файлам — переменная FilePath хранит путь к входному файлу, который может быть изменён без редактирования пакета.
  • Управление итерациями — переменная Counter используется в цикле For для обработки заданного количества элементов.
  • Хранение промежуточных результатов — переменная RowCount накапливает количество обработанных строк для последующего логирования.
  • Условная логика — переменная IsError устанавливается в True при возникновении ошибки, и на основе её значения выбирается ветвь выполнения.

Ограничения и особенности

  • Переменные не могут быть использованы для хранения больших объёмов данных (например, целых таблиц) — для этого предназначены табличные объекты.
  • Тип Object позволяет хранить любые данные, но требует явного преобразования в скриптовых задачах.
  • Область видимости переменной не может быть изменена после создания; для изменения области требуется создать новую переменную.
  • В SSIS 2008 и более ранних версиях отсутствовала поддержка параметров, и передача значений между пакетами осуществлялась через родительские переменные или конфигурации.

Источники

  • Microsoft Docs: «Integration Services (SSIS) Variables» (официальная документация)
  • «SQL Server Integration Services (SSIS) Step by Step» — книга Microsoft Press
  • «Professional Microsoft SQL Server 2012 Integration Services» — Wrox Press
  • Статья «Using Variables in SSIS Packages» на SQLServerCentral

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

На главную BFOmetr →

Похожие материалы