Поделиться через

Переменные в службах Integration Services (SSIS)

  • Статья

Область применения: среда выполнения интеграции SSIS SQL Server в Фабрика данных Azure

Переменные хранят значения, которые пакет СЛУЖБ SQL Server Integration Services и его контейнеры, задачи и обработчики событий могут использовать во время выполнения. Скрипты в задаче «Скрипт» и компоненте скрипта также могут использовать переменные. Управления очередностью, которые упорядочивают задачи и контейнеры в рабочий процесс, могут использовать переменные, если связанные с ними определения ограничений включают в себя выражения.

Переменные в пакетах служб Integration Services можно использовать в следующих целях:

  • Обновление свойств элементов пакета во время выполнения. Например, можно динамически задать количество параллельно исполняемых объектов, которые допускаются контейнером «цикл по каждому элементу».

  • Включение таблицы уточняющих запросов в памяти. Например, пакет может запустить задачу «Выполнение SQL», загружающую переменную со значениями данных.

  • Загрузка переменных со значениями данных и использование их для задания условий поиска в предложении WHERE. Например, скрипт в задаче «Скрипт» может обновить значение переменной, которая используется инструкцией Transact-SQL в задаче «Выполнение SQL».

  • Присвоение переменной значения целого числа и использование этого значения для управления циклом в потоке управления пакетом. Например, вычисляемое выражение в контейнере «цикл по каждому элементу» может использовать переменную для управления итерацией.

  • Заполнение значений параметров инструкций Transact-SQL во время выполнения. Например, пакет может запустить задачу «Выполнение SQL» и затем динамически задавать параметры в инструкции Transact-SQL.

  • Построение выражений, содержащих значения переменных. Например, преобразование «Производный столбец» может заполнить столбец результатами, полученными путем умножения значения переменной на значение столбца.

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

Службы Integration Services поддерживают два типа переменных: определяемые пользователем переменные и системные переменные. Определяемые пользователем переменные определяются разработчиками пакетов, а системные переменные определяются службами Integration Services. Допустимо создание в пакете любого необходимого количества пользовательских переменных, однако дополнительные системные переменные не могут быть созданы.

Все переменные — как системные, так и определенные пользователем — могут быть использованы в связывании параметров, которые использует задача "Выполнение SQL" для сопоставления переменных параметрам в инструкциях SQL. Дополнительные сведения см. в разделах Задача "Выполнение SQL" и Параметры и коды возврата в задаче "Выполнение SQL".

Примечание.

В именах пользовательских и системных переменных учитывается регистр.

Вы можете создавать определяемые пользователем переменные для всех типов контейнеров служб Integration Services: пакеты, контейнеры циклов foreach, контейнеры циклов, контейнеры последовательности, задачи и обработчики событий. Пользовательские переменные являются элементами коллекции Variables соответствующего контейнера.

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

Пользовательские переменные можно настроить следующими способами:

  • задать имя и описание переменной;

  • указать пространство имен для переменной;

  • указать, инициируется ли событие при изменении значения переменной;

  • указать, доступна ли переменная только для чтения или для чтения и записи;

  • использовать для задания значения переменной результат вычисления выражения;

  • создать переменную в области видимости пакета или объекта пакета, например задачи;

  • указать значение и тип данных переменной.

Для системной переменной доступен лишь один настраиваемый параметр: можно выбрать, инициируется ли событие при изменении ее значения.

Для разных типов контейнеров доступен разный набор системных переменных. Дополнительные сведения о системных переменных, используемых в пакетах и их элементах, см. в разделе System Variables.

Дополнительные сведения о практическом использовании переменных см. в разделе Использование переменных в пакетах.

Свойства переменных

Свойства пользовательских переменных можно установить в окне Переменные или окне Свойства . Некоторые свойства доступны только в окне «Свойства»

Примечание.

Для системной переменной доступен лишь один настраиваемый параметр: можно выбрать, инициируется ли событие при изменении ее значения.

Description
Задает описание переменной.

EvaluateAsExpression
Если свойство имеет значение True, указанное выражение используется для задания значения переменной.

Выражение
Указывает выражение, назначаемое переменной.

Имя
Определяет имя переменной.

Пространство имен
Службы Integration Services предоставляют два пространства имен, пользователя и систему. По умолчанию пользовательские переменные содержатся в пространстве имен User , а системные переменные — в пространстве имен System . Можно создать дополнительные пространства имен для пользовательских переменных и переименовать пространство имен User , однако переименовать пространство имен System , добавлять переменные в пространство имен System или назначить системные переменные другому пространству имен нельзя.

RaiseChangedEvent
Если это свойство имеет значение True, то при изменении значения переменной возникает событие OnVariableValueChanged .

ReadOnly
Если это свойство имеет значение False, переменная доступна для чтения и записи.

Область применения

Примечание.

Это свойство можно изменить только в окне Переменные , нажав кнопку Переместить переменную .

Переменная создается в области пакета либо в области контейнера, задачи или обработчика события, содержащегося в этом пакете. Поскольку контейнер пакета находится на верхнем уровне иерархии контейнеров, переменные в области пакета ведут себя как глобальные переменные и могут использоваться во всех контейнерах пакета. Аналогично, переменные определенные в области контейнера, такого как «цикл по элементам», могут использоваться всеми задачами или контейнерами внутри контейнера «цикл по элементам».

Если пакет запускает другие пакеты при помощи задачи «Выполнение пакета», то переменные, определенные в области вызывающего пакета или задачи выполнения пакета, могут быть сделаны доступными для вызываемого пакета при помощи конфигурации «Переменная родительского пакета». Дополнительные сведения см. в статье Package Configurations.

IncludeInDebugDump
Указывает, включается ли значение переменной в отладочные файлы дампа.

По умолчанию для системных и определенных пользователем переменных параметр InclueInDebugDump по умолчанию имеет значение true.

Однако для переменных, определенных пользователем, система сбрасывает параметр IncludeInDebugDump в значение false при соблюдении следующих условий:

  • Если свойство переменной EvaluateAsExpression имеет значение true, то система устанавливает для параметра IncludeInDebugDump значение false.

    Чтобы включить текст выражения в отладочные файлы дампа в качестве переменной, установите параметр IncludeInDebugDump в значение true.

  • При смене типа данных переменной на строковый система устанавливает параметр IncludeInDebugDump в значение false.

Когда система сбрасывает параметр IncludeInDebugDump в значение false, выбранное пользователем значение может быть переопределено.

Value
Значение пользовательской переменной может быть задано строкой или выражением. Переменная не может иметь значение NULL. Переменные имеют следующие значения по умолчанию.

Тип данных Default value
Логический False
Числовые и двоичные типы данных 0 (ноль)
Символьные и строковые типы данных (пустая строка)
Object System.Object

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

Если переменная настроена как результат выражения, необходимо задать выражение. Во время выполнения выражение вычисляется, и переменной присваивается результат вычисления. Например, если переменная использует выражение DATEPART("month", GETDATE()) , значением переменной является число, равное месяцу текущей даты. Выражение должно быть допустимым выражением, использующим синтаксис грамматики выражений SSIS. Если выражение используется с переменными, оно может использовать литералы, операторы и функции, допустимые грамматикой выражений, но не может ссылаться на столбцы в потоке данных пакета. Максимальная длина выражения составляет 4000 символов. Дополнительные сведения см. в разделе Выражения Integration Services (SSIS).

ValueType

Примечание.

Значение свойства отображается в столбце Тип данных окна Переменные .

Задает тип данных для значения переменной.

Сценарии использования переменных

Переменные используются различными способами в пакетах служб Integration Services. Возможно, что разработка пакетов будет затруднительна без добавления в пакет пользовательских переменных для реализации гибкости и управляемости решений. В зависимости от сценария также используются системные переменные.

Выражение свойств. Используйте переменные для предоставления значений в выражениях свойств, устанавливающих свойства пакетов и объектов пакетов. Например, выражение SELECT * FROM @varTableName включает переменную varTableName , которая обновляет инструкцию SQL, выполняемую задачей «Выполнение SQL». Выражение DATEPART("d", GETDATE()) == 1? @[User::varPackageFirst]:@[User::varPackageOther]обновляет пакет, выполняемый задачей «Выполнение пакета», запуская пакет, указанный в переменной varPackageFirst , в первый день месяца и пакет, указанный в переменной varPackageOther , в остальные дни. Дополнительные сведения см. в разделе Использование выражений свойств в пакетах.

Выражения потоков данных. Используйте переменные для предоставления значений выражениям, используемым преобразованиями «Производный столбец» и «Условное разбиение» для заполнения столбцов или направления строк на различные выходы преобразования. Например, выражение @varSalutation + LastNameобъединяет значение переменной VarSalutation и столбец LastName . Выражение Income < @HighIncomeнаправляет на выход строки данных, в которых значение столбца Income меньше значения переменной HighIncome . Дополнительные сведения см. в статьях Преобразование "Производный столбец", Преобразование "Условное разбиение" и Выражения служб Integration Services (SSIS).

Выражения управления очередностью. Предоставляют значения для использования в управлении очередностью для определения запуска исполняемого объекта с таким управлением. Эти выражения могут использоваться вместе с результатом выполнения (успех, ошибка, завершение) либо вместо результата выполнения. Например, если выражение @varMax > @varMinполучает значение true, исполняемый объект запускается. Дополнительные сведения см. в статье Добавление выражений к элементам управления очередностью.

Параметры и коды возврата. Предоставляют значения для ввода параметров или сохраняют значения выходных параметров и кодов возврата. Это можно сделать с помощью сопоставления переменных с параметрами и возвращаемыми значениями. Например, если переменная varProductId имеет значение 23 при выполнении инструкции SQL SELECT * from Production.Product WHERE ProductID = ?, запрос получит продукты с идентификатором ProductID , равным 23. Дополнительные сведения см. в разделах Задача "Выполнение SQL" и Параметры и коды возврата в задаче "Выполнение SQL".

Выражения контейнера «цикл по элементам». Предоставляют значения для использования выражений инициализации, оценки и присваивания в контейнере «цикл по элементам». Например, если переменная varCount имеет значение 2, а varMaxCount — 10, выражение инициализации будет иметь значение @varCount, выражение оценки — @varCount < @varMaxCount, а выражение присваивания — @varCount =@varCount +1, при этом цикл повторится 8 раз. Дополнительные сведения см. в статье For Loop Container.

Конфигурации переменной родительского пакета. Передает значения из родительского пакета в дочерний. Дочерние пакеты могут получить доступ к переменным родительского пакета путем использования конфигураций переменных пакета. Например, если дочерний пакет должен использовать ту же дату, что и родительский пакет, он может определить конфигурацию переменной родительского пакета, которая определяет значение переменной, установленное с помощью функции GETDATE. Дополнительные сведения см. в разделах Execute Package Task и Package Configurations.

Задача скрипта и компонент скрипта . Предоставляет список переменных только для чтения и чтения/записи для задач "Скрипт" и компонента скрипта, обновляет переменные для чтения/записи с помощью скрипта и использует обновленные значения в скрипте или вне его. Например, в коде numberOfCars = CType(Dts.Variables("NumberOfCars").Value, Integer)переменная скрипта numberOfCars обновлена значением переменной NumberOfCars. Дополнительные сведения см. в статье Using Variables in the Script Task.

Добавление переменной

  1. В SQL Server Data Tools (SSDT) откройте пакет служб Integration Services, с которыми вы хотите работать.

  2. Чтобы открыть пакет, дважды щелкните его в обозревателе решений.

  3. Чтобы определить область переменной в конструкторе служб SSIS, выполните одно из следующих действий:

    • Чтобы установить в качестве области область пакета, щелкните в любом месте области конструктора на вкладке Поток управления .

    • Чтобы установить в качестве области область обработчика события, выберите исполняемый объект и обработчик событий в области конструктора на вкладке Обработчик событий .

    • Чтобы установить в качестве области область задачи или контейнера, щелкните задачу или контейнер в области конструктора на вкладке Поток управления или вкладке Обработчик события .

  4. В меню Службы SSIS щелкните Переменные. При необходимости окно Переменные можно открыть, назначив команде View.Variables нужное сочетание клавиш на странице Клавиатура диалогового окна Параметры .

  5. В окне Переменные щелкните значок Добавить переменную . Новая переменная будет добавлена в список.

  6. Также можно щелкнуть значок Параметры сетки , выбрать дополнительные столбцы для отображения в диалоговом окне Параметры сетки переменных и нажать кнопку ОК.

  7. Если необходимо, установите свойства переменной. Дополнительные сведения см. в разделе Установка свойств определяемой пользователем переменной.

  8. Чтобы сохранить обновленный пакет, выберите пункт Сохранить выбранные элементы в меню Файл .

диалоговое окно «Добавление переменной»

Используйте диалоговое окно Добавление переменной , чтобы указать свойства новой переменной.

Параметры

Контейнер
Выберите контейнер из списка. Контейнер определяет область видимости переменной. Контейнер может быть пакетом или исполняемым объектом в пакете.

Имя
Введите имя переменной.

Пространство имен
Укажите пространство имен переменной. По умолчанию пользовательские переменные находятся в пространстве имен User .

Тип значения
Выберите тип данных.

Value
Введите значение. Значение должно быть совместимо с типом данных, указанным для параметра Тип значения .

Только для чтения
Выберите этот пункт, чтобы сделать переменную доступной только для чтения.

Удаление переменной

  1. В SQL Server Data Tools (SSDT) откройте проект служб Integration Services, содержащий нужный пакет.

  2. В окне обозревателя решений щелкните пакет правой кнопкой мыши для его открытия.

  3. В меню Службы SSIS щелкните Переменные. При необходимости окно Переменные можно открыть, назначив команде View.Variables нужное сочетание клавиш на странице Клавиатура диалогового окна Параметры .

  4. Выберите удаляемую переменную и нажмите кнопку Удалить переменную.

    Если переменная не отображается в окне "Переменные", щелкните Параметры сетки и выберите Показать переменные всех областей.

  5. Если открывается диалоговое окно Подтверждение удаления переменных , для подтверждения нажмите кнопку Да .

  6. Чтобы сохранить обновленный пакет, выберите пункт Сохранить выбранные элементы в меню Файл .

Изменение области видимости переменной

  1. В SQL Server Data Tools (SSDT) откройте проект служб Integration Services, содержащий нужный пакет.

  2. В окне обозревателя решений щелкните пакет правой кнопкой мыши для его открытия.

  3. В меню Службы SSIS щелкните Переменные. При необходимости окно Переменные можно открыть, назначив команде View.Variables нужное сочетание клавиш на странице Клавиатура диалогового окна Параметры .

  4. Выберите переменную и нажмите кнопку Переместить переменную.

    Если переменная не отображается в окне "Переменные", щелкните Параметры сетки и выберите Показать переменные всех областей.

  5. В диалоговом окне Выбор новой области выберите пакет, контейнер, задачу или обработчик событий в пакете, чтобы изменить область переменной.

  6. Чтобы сохранить обновленный пакет, выберите пункт Сохранить выбранные элементы в меню Файл .

Установка свойств определяемой пользователем переменной

Чтобы задать свойства определяемой пользователем переменной в службах Integration Services, можно использовать одну из следующих функций:

  • Окно «Переменные».

  • Окно «Свойства». В окне Свойства указаны свойства для настройки переменных, недоступные в окне Переменные : Description, EvaluateAsExpression, Expression, ReadOnly, ValueType и IncludeInDebugDump.

Примечание.

Службы Integration Services также предоставляют набор системных переменных, свойства которых нельзя обновить, за исключением свойства RaiseChangedEvent.

Задание выражений в переменных

При использовании окна Свойства для задания выражений на определяемой пользователем переменной:

  • Значение переменной можно задать с помощью свойства Value или Expression. По умолчанию значение свойства EvaluateAsExpression равно False , а значение переменной задается свойством Value. Чтобы использовать выражения для задания значения, необходимо прежде установить свойство EvaluateAsExpression в значение True, а затем указать выражение в свойстве Expression. Свойство Value автоматически устанавливается в значение результата выражения.

  • Свойство ValueType содержит тип данных значения свойства Value. Если свойство Value задается с помощью выражения, свойство ValueType автоматически обновляется типом данных, совместимым с результатом вычисления выражения. Например, если свойство Value содержит 0, а свойство ValueType — Int32 , а затем свойству Expression присваивается GETDATE(), то Value содержит текущую дату и время, а свойство ValueType устанавливается в DateTime.

  • Окно Свойства для переменной предоставляет доступ к диалоговому окну Построитель выражений . Это средство можно использовать для построения, проверки и вычисления выражений. Дополнительные сведения см. в статьях Построитель выражений и Выражения служб Integration Services (SSIS).

При использовании окна Переменные для задания выражений на определяемой пользователем переменной:

  • Чтобы использовать выражение для задания значения переменной, сначала убедитесь, что тип данных переменной совместим с результатом вычисления выражения, и введите выражение в столбце Выражение окна Переменные . Свойство EvaluateAsExpression в окне Свойства автоматически задается равным True.

  • При присваивании выражения переменной рядом с переменной отображается специальный маркер значка. Этот специальный маркер значка отображается также рядом с диспетчерами соединений и задачами, для которых заданы выражения.

  • Окно Переменные для переменной предоставляет доступ к диалоговому окну Построитель выражений . Это средство можно использовать для построения, проверки и вычисления выражений. Дополнительные сведения см. в статьях Построитель выражений и Выражения служб Integration Services (SSIS).

Нельзя изменить тип данных переменной ни в окне Переменные , ни в окне Свойства , если переменной назначено выражение, а свойство EvaluateAsExpression задано как True, .

Задание свойств пространства имен и имени

Первым символом в значениях свойств Имя и Пространство имен согласно стандарту Юникод 2.0 должна быть буква или символ подчеркивания (_). Последующие символы могут быть буквами или цифрами, как определено в Стандарте Юникод 2.0, или символами подчеркивания (_).

Задание свойств переменной в окне "Переменные"

  1. В SQL Server Data Tools (SSDT) откройте проект служб Integration Services, содержащий нужный пакет.

  2. В окне обозревателя решений щелкните пакет правой кнопкой мыши для его открытия.

  3. В меню Службы SSIS щелкните Переменные.

    При необходимости окно Переменные можно открыть, назначив команде View.Variables нужное сочетание клавиш на странице Клавиатура диалогового окна Параметры .

  4. При желании в окне Переменные выберите Параметры сетки, а затем выберите столбцы для отображения в окне Переменные и выберите фильтры для списка переменных.

  5. Выберите переменную в списке, а затем обновите значения в столбцах Имя, Тип данных, Значение, Пространство имен, Создать событие изменения, Описание и Выражение .

  6. Выберите переменную из списка, а затем нажмите Переместить переменную для изменения области.

  7. Чтобы сохранить измененный пакет, в меню Файл выберите команду Сохранить выбранные элементы.

Задание свойств переменной в окне "Свойства"

  1. В SQL Server Data Tools (SSDT) откройте проект служб Integration Services, содержащий нужный пакет.

  2. В окне обозревателя решений щелкните пакет правой кнопкой мыши для его открытия.

  3. В меню Просмотр выберите пункт Окно свойств.

  4. В конструкторе служб SSIS щелкните вкладку обозревателя пакетов и разверните узел пакета.

  5. Чтобы изменить переменные в области пакета, разверните узел «Переменные» или разворачивайте узлы «Обработчики событий» или «Исполняемые объекты», пока не обнаружите узел «Переменные» с переменной, которую необходимо изменить.

  6. Щелкните переменную, свойства которой необходимо изменить.

  7. В окне Свойства обновите свойства переменной для чтения/записи. Для переменных, определяемых пользователем, некоторые свойства отображаются только для чтения.

    Дополнительные сведения о свойствах см. в статье Переменные в службах Integration Services (SSIS).

  8. Чтобы сохранить измененный пакет, в меню Файл выберите команду Сохранить выбранные элементы.

Динамическое обновление переменной с помощью конфигураций

Чтобы динамически обновлять переменные, можно создавать конфигурации для переменных, развертывать их вместе с пакетами и обновлять значения переменных в файле конфигурации при развертывании пакета. Во время выполнения пакет будет использовать обновленные значения переменных. Дополнительные сведения см. в разделе Создание конфигурации пакетов.

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

Сопоставления параметров запросов с переменными в компонентах потока данных