Язык выражений SSIS

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

История и развитие

Язык выражений SSIS был введён вместе с первой версией SQL Server Integration Services в составе SQL Server 2005. До этого в SQL Server 2000 использовалась технология Data Transformation Services (DTS), где для аналогичных целей применялись скрипты на VBScript или JScript. С переходом на SSIS разработчики Microsoft создали специализированный язык выражений, ориентированный на обработку данных в потоке (data flow) и управление логикой выполнения пакетов.

В последующих версиях SQL Server (2008, 2012, 2016, 2019, 2022) язык выражений SSIS претерпел незначительные изменения: добавлялись новые функции, расширялась поддержка типов данных, улучшалась обработка дат и строк. Основные принципы языка остались неизменными, что обеспечило обратную совместимость пакетов.

Синтаксис и типы данных

Язык выражений SSIS использует C-подобный синтаксис с операторами, функциями и литералами. Выражения всегда возвращают одно значение определённого типа.

Типы данных

Язык поддерживает следующие типы данных:

• Boolean — логическое значение (True/False).
• Integer — 32-битное целое число со знаком (диапазон от -2 147 483 648 до 2 147 483 647).
• Unsigned Integer — 32-битное целое число без знака (от 0 до 4 294 967 295).
• Int64 — 64-битное целое число со знаком.
• Unsigned Int64 — 64-битное целое число без знака.
• Float — 64-битное число с плавающей запятой (double).
• Decimal — точное десятичное число с фиксированной точностью и масштабом.
• String — последовательность символов Unicode (UTF-16).
• DateTime — дата и время (диапазон от 1 января 1753 года до 31 декабря 9999 года).
• DT_NTEXT — длинный строковый тип (до 2^30 символов).
• DT_IMAGE — двоичные данные (до 2^31 байт).
• DT_GUID — глобальный уникальный идентификатор (GUID).
• DT_DBDATE — только дата (без времени).
• DT_DBTIME — только время (без даты).
• DT_DBTIMESTAMP — дата и время с точностью до наносекунд.

Литералы

Литералы записываются в зависимости от типа:

• Строковые: "Hello, World!" (в двойных кавычках).
• Числовые: 123, -45.67.
• Логические: TRUE, FALSE.
• Дата и время: "2025-04-08 14:30:00" (в строковом формате, преобразуются функцией DT_DBTIMESTAMP).

Операторы

Язык поддерживает стандартные операторы:

• Арифметические: +, -, *, /, % (остаток от деления).
• Сравнения: ==, !=, >, <, >=, <=.
• Логические: && (AND), || (OR), ! (NOT).
• Конкатенация строк: +.
• Приведение типов: явное приведение с помощью функций (DT_...).

Функции

Язык выражений SSIS включает более 100 встроенных функций, разделённых на категории.

Функции для работы со строками

• LEN(string) — возвращает длину строки.
• UPPER(string) — преобразует строку в верхний регистр.
• LOWER(string) — преобразует строку в нижний регистр.
• SUBSTRING(string, start, length) — извлекает подстроку.
• REPLACE(string, old, new) — заменяет подстроку.
• TRIM(string) — удаляет пробелы в начале и конце.
• FINDSTRING(string, search, occurrence) — находит позицию вхождения подстроки.
• TOKEN(string, delimiter, token_number) — извлекает токен по разделителю.

Функции для работы с датами и временем

• GETDATE() — возвращает текущую дату и время.
• DATEADD(datepart, number, date) — добавляет интервал к дате.
• DATEDIFF(datepart, startdate, enddate) — вычисляет разницу между датами.
• DATEPART(datepart, date) — извлекает часть даты (год, месяц, день и т.д.).
• DAY(date), MONTH(date), YEAR(date) — извлекают соответствующие компоненты.

Математические функции

• ABS(number) — абсолютное значение.
• CEILING(number) — округление вверх.
• FLOOR(number) — округление вниз.
• ROUND(number, precision) — округление до заданной точности.
• POWER(base, exponent) — возведение в степень.
• SQUARE(number) — квадрат числа.
• SQRT(number) — квадратный корень.

Функции преобразования типов

• (DT_I4) — приведение к 32-битному целому.
• (DT_STR, length, codepage) — приведение к строке ANSI.
• (DT_WSTR, length) — приведение к строке Unicode.
• (DT_DBTIMESTAMP) — приведение к дате и времени.
• (DT_DECIMAL, precision, scale) — приведение к десятичному числу.

Функции для работы с NULL

• ISNULL(expression) — проверка на NULL.
• NULL — возвращает NULL-значение.
• REPLACENULL(expression, replacement) — заменяет NULL на указанное значение.

Логические функции

• IIF(condition, true_part, false_part) — условное выражение (аналог тернарного оператора).
• SWITCH(expression, value1, result1, ..., default) — выбор из нескольких вариантов.

Применение в SSIS

Язык выражений SSIS используется в трёх основных контекстах:

1. Задание значений переменных

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

`` @[User::FileName] = "Report_" + (DT_WSTR, 4) YEAR(GETDATE()) + ".csv" ``

2. Настройка свойств компонентов

Многие компоненты потока данных (например, «Условное разбиение», «Производный столбец», «Преобразование «Скрипт»») и контейнеры (цикл «Foreach», «For») используют выражения для задания условий, формул и параметров.

• Производный столбец — создаёт новые столбцы на основе выражений.
• Условное разбиение — направляет строки в разные выходы по условию.
• Преобразование «Скрипт» — может использовать выражения для инициализации переменных.

3. Динамическое конфигурирование пакетов

Выражения применяются для задания строк подключения, путей к файлам, параметров SQL-запросов и других свойств на этапе выполнения пакета. Это позволяет делать пакеты гибкими и переносимыми между средами (разработка, тестирование, продуктив).

Примеры выражений

Пример 1. Формирование пути к файлу с датой

`` "\\\\server\\share\\data_" + (DT_WSTR, 4) YEAR(GETDATE()) + "_" + RIGHT("0" + (DT_WSTR, 2) MONTH(GETDATE()), 2) + ".csv" ``

Результат: \\server\share\data_2025_04.csv

Пример 2. Проверка на NULL и замена

`` REPLACENULL(LastName, "Unknown") ``

Пример 3. Условное разбиение

`` [TotalAmount] > 1000 && [CustomerType] == "VIP" ? "High Priority" : "Standard" ``

Пример 4. Извлечение года из даты

`` YEAR([OrderDate]) ``

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

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

Сравнение с другими языками

В отличие от скриптовых языков (C#, VB.NET), которые могут использоваться в компоненте «Скрипт», язык выражений SSIS не требует компиляции и выполняется интерпретатором. Это делает его более быстрым для простых преобразований, но менее гибким для сложной логики. Для задач, требующих циклов, условных переходов или доступа к внешним ресурсам, рекомендуется использовать компонент «Скрипт» на C#.

Источники

• Microsoft Docs: «Integration Services (SSIS) Expressions»
• Microsoft Docs: «SSIS Expression Reference»
• «Microsoft SQL Server 2012 Integration Services» — Brian Knight, Erik Veerman, et al.
• «Professional Microsoft SQL Server 2016 Integration Services» — Brian Knight, Erik Veerman, et al.