Строковые функции SSIS

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

Типы строковых функций

Строковые функции SSIS делятся на несколько групп по своему назначению:

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

• UPPER(character_expression) — преобразует все буквы в строке в верхний регистр.
• LOWER(character_expression) — преобразует все буквы в строке в нижний регистр.
• REVERSE(character_expression) — переворачивает строку задом наперёд.

Функции извлечения и поиска

• LEFT(character_expression, integer_expression) — возвращает указанное количество символов с начала строки.
• RIGHT(character_expression, integer_expression) — возвращает указанное количество символов с конца строки.
• SUBSTRING(character_expression, start, length) — извлекает подстроку заданной длины, начиная с указанной позиции.
• CHARINDEX(expression_to_find, character_expression [, start_location]) — возвращает начальную позицию первого вхождения одной строки в другую. Если подстрока не найдена, возвращает 0.
• TOKEN(character_expression, delimiter_string, token_number) — извлекает n-й фрагмент строки, разделённый заданным разделителем.

Функции удаления пробелов

• TRIM(character_expression) — удаляет начальные и конечные пробелы.
• LTRIM(character_expression) — удаляет только начальные пробелы.
• RTRIM(character_expression) — удаляет только конечные пробелы.

Функции замены и форматирования

• REPLACE(character_expression, search_string, replacement_string) — заменяет все вхождения одной подстроки на другую.
• REPLICATE(character_expression, integer_expression) — повторяет строку заданное количество раз.
• SPACE(integer_expression) — возвращает строку, состоящую из указанного количества пробелов.
• CODEPOINT(character_expression) — возвращает числовой код первого символа строки в кодировке Unicode (UTF-16LE).
• UNICODE(character_expression) — возвращает целочисленное значение (код символа) для первого символа строки в кодировке Unicode.

Функции проверки и сравнения

• FINDSTRING(character_expression, search_string, occurrence) — возвращает начальную позицию указанного вхождения подстроки (например, второго или третьего). Если вхождение не найдено, возвращает 0.
• HEX(integer_expression) — преобразует целое число в шестнадцатеричную строку.

Синтаксис и использование

Строковые функции в SSIS используются в редакторе выражений (Expression Builder), который доступен для большинства компонентов пакета. Выражения могут быть написаны на языке SSIS Expression Language, который поддерживает смешивание функций, операторов и констант.

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

1. Извлечение имени файла из полного пути:

TOKEN(@[User::FilePath], "\\", TOKENCOUNT(@[User::FilePath], "\\")) Здесь TOKENCOUNT — это функция, возвращающая количество фрагментов в строке при разбиении по указанному разделителю.

1. Преобразование текста в нижний регистр:

LOWER(SUBSTRING(FirstName, 1, 1)) + LOWER(SUBSTRING(FirstName, 2, LEN(FirstName) - 1)) В данном примере первая буква остаётся в верхнем регистре, остальные приводятся к нижнему.

1. Замена недопустимых символов:

REPLACE(REPLACE(ColumnName, "/", "_"), "\\", "_") Заменяет косую и обратную косую черту на символ подчёркивания.

1. Проверка длины строки:

LEN(TRIM(ColumnName)) > 0 Возвращает TRUE, если строка после удаления пробелов не пуста.

Типы данных и неявные преобразования

Строковые функции SSIS работают с типами данных DT_STR (строки ANSI) и DT_WSTR (строки Unicode). При использовании функций в выражениях SSIS автоматически выполняет неявные преобразования типов, если это возможно. Например, целое число может быть преобразовано в строку при конкатенации с помощью оператора +. Однако для явного преобразования рекомендуется использовать функции (DT_STR, codepage) или (DT_WSTR).

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

• Чувствительность к регистру: По умолчанию функции CHARINDEX и FINDSTRING чувствительны к регистру. Для регистронезависимого поиска требуется предварительно привести обе строки к одному регистру с помощью UPPER или LOWER.
• Индексация символов: В SSIS позиции символов в строке начинаются с 1, а не с 0, как в некоторых языках программирования.
• Обработка NULL: Если любой аргумент строковой функции имеет значение NULL, функция возвращает NULL. Это важно учитывать при построении выражений с условной логикой — рекомендуется использовать функцию ISNULL для проверки.
• Кодировка: Для строк Unicode (DT_WSTR) функции работают с двухбайтовыми символами; для ANSI (DT_STR) — с однобайтовыми. Функция LEN для DT_WSTR возвращает количество символов, а не байтов.

Применение в пакетах SSIS

Строковые функции активно используются в следующих сценариях:

• Очистка данных: удаление лишних пробелов, замена некорректных символов, исправление регистра.
• Извлечение информации: парсинг составных полей (например, ФИО, адресов, путей к файлам).
• Форматирование вывода: подготовка строк для отчётов, файлов или сообщений об ошибках.
• Условная логика: проверка вхождений подстрок (например, CHARINDEX("error", ErrorMessage) > 0).
• Генерация ключей: создание составных ключей на основе конкатенации полей.

Сравнение с функциями T-SQL

Хотя SSIS и T-SQL (Transact-SQL) являются продуктами Microsoft, их строковые функции имеют различия:

SSIS не поддерживает регулярные выражения напрямую — для сложного сопоставления шаблонов требуется использовать скриптовый компонент (Script Component) на языке C# или VB.NET.

Производительность

При обработке больших объёмов данных (миллионы строк) частое использование строковых функций может снижать производительность пакета. Рекомендуется минимизировать количество вызовов функций в одном выражении, избегать избыточных преобразований типов и, по возможности, выполнять ресурсоёмкие операции (например, REPLACE или CHARINDEX) на этапе предварительной обработки в источнике данных (например, в SQL-запросе).

Источники

• Microsoft Learn: SSIS Expression Language Functions (String Functions)
• Microsoft SQL Server Integration Services Documentation (SQL Server 2019, 2022)
• Руководство по SSIS: выражения и функции (TechNet, MSDN)
• Практическое руководство по ETL-процессам в SSIS (издания Microsoft Press)