Задача "Выполнение SQL"

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

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

• Очистите таблицу или представление перед вставкой данных.

• создание, изменение и удаление объектов базы данных, таких как таблицы и представления;

• повторное создание таблиц фактов и таблиц измерений перед загрузкой данных;

• Запустите хранимые процедуры. Если инструкция SQL вызывает хранимую процедуру, возвращающую результаты из временной таблицы, используйте параметр WITH RESULT SETS для определения метаданных набора результатов.

• Сохраните набор строк, возвращаемый запросом, в переменную.

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

Подключение к источникам данных

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

Тип подключения	Диспетчер соединений
EXCEL	Диспетчер подключений Excel
OLE DB	Диспетчер подключений OLE DB
ODBC	Диспетчер подключений ODBC
ADO	Диспетчер подключений ADO
ADO.NET	Диспетчер подключений ADO.NET
SQLMOBILE	Диспетчер подключений SQL Server Compact Edition

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

Источником инструкций SQL для этой задачи может быть свойство задачи, которое содержит инструкцию, соединение с файлом, содержащим инструкции, или имя переменной, хранящей инструкцию. Для написания инструкций SQL необходимо использовать разновидность языка SQL, используемую системой управления базой данных-источником (СУБД). Дополнительные сведения см. в разделе Запросы служб Integration Services (SSIS).

Если инструкции SQL хранятся в файле, задача использует диспетчер подключения файлов для подключения к файлу. Дополнительные сведения см. в статье File диспетчер подключений.

В конструкторе служб SSIS можно использовать диалоговое окно редактора задач Execute SQL для ввода инструкций SQL или использования построителя запросов, графического пользовательского интерфейса для создания запросов SQL.

Примечание.

Допустимые инструкции SQL, написанные вне задачи «Выполнение SQL», могут не быть успешно проанализированы задачей «Выполнение SQL».

Примечание.

Задача «Выполнение SQL» использует значение перечисления ParseMode RecognizeAll. Дополнительные сведения см. в разделе Пространство имен ManagedBatchParser.

Отправка нескольких инструкций в пакете

Если в задачу «Выполнение SQL» включить несколько инструкций, их можно сгруппировать и запускать как пакет. Для обозначения окончания пакета используется команда GO. Все инструкции SQL, находящиеся между двумя командами GO, отправляются в одном пакете поставщику OLE DB для выполнения. Команда SQL может содержать несколько пакетов, разделенных командами GO.

Существуют ограничения на тип инструкций SQL, которые могут объединяться в пакеты. Дополнительные сведения см. в разделе Batches of Statements.

Если задача «Выполнение SQL» выполняет пакет инструкций SQL, к пакету применяются следующие правила:

• Только одна инструкция может возвращать результирующий набор. Эта инструкция должна быть первой в пакете.

• Если в результирующем наборе содержатся связанные столбцы, запросы должны возвращать такое же количество столбцов. Если запросы возвращают разное количество столбцов, происходит сбой выполнения задачи. Однако даже если задача завершается сбоем, запросы, которые он выполняет, например DELETE или INSERT запросы, могут завершиться успешно.

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

• Если в задаче используются связывания параметров, у всех запросов в пакете должны быть одинаковые типы параметров и их количество.

Выполнение параметризованных команд SQL

В инструкциях SQL и хранимых процедурах часто используются входные параметры, выходные параметры и коды возврата. Задача «Выполнение SQL» поддерживает типы параметров Input, Outputи ReturnValue . Используйте тип Input для входных параметров, Output — для выходных и ReturnValue — для кодов возврата.

Примечание.

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

Укажите тип результирующего набора

В зависимости от команды SQL задаче «Выполнение SQL» может быть возвращен (или не возвращен) результирующий набор. Например, инструкция SELECT обычно возвращает результирующий набор, а инструкция INSERT — нет. Результирующий набор инструкции SELECT может не содержать ни одной строки, содержать одну строку или несколько строк. Хранимые процедуры возвращают целочисленные значения, называемые кодом возврата, который отражает состояние выполнения процедуры. В этом случае результирующий набор состоит из одной строки.

Настройка задачи "Выполнение SQL"

Настроить задачу «Выполнение SQL» можно одним из следующих способов:

• Указать тип диспетчера соединений для подключения к базе данных.

• Указать тип результирующего набора, возвращаемого инструкцией SQL.

• Указать время ожидания для инструкции SQL.

• Указать источник для инструкции SQL.

• Указать, должна ли задача пропустить фазу подготовки инструкции SQL.

• При использовании типа соединения ADO необходимо указать, является ли инструкция SQL хранимой процедурой. Для других типов соединений это свойство доступно только для чтения и всегда имеет значение false.

Свойства можно задать программным способом или с помощью конструктора служб SSIS.

Страница «Общие» — редактор задачи выполнения SQL

Используйте страницу Общие диалогового окна Редактор задачи «Выполнение SQL» для настройки задачи «Выполнение SQL» и формирования инструкции SQL, которую запускает задача.

Дополнительные сведения о языке запросов Transact-SQL см. в Справочнике по Transact-SQL (ядро СУБД).

Статические параметры

Имя
Укажите уникальное имя для задачи «Выполнение SQL» в рабочем процессе. Указанное имя будет отображаться в SSIS Designer.

Description
Приведите описание задачи «Выполнение SQL». Рекомендуется описать назначение задачи, чтобы сделать пакеты самодокументируемыми и более простыми в обслуживании.

Тайм-аут
Укажите максимальное количество секунд, в течение которых задача будет выполняться до истечения времени ожидания. Значение 0 указывает бесконечное время. По умолчанию установлено значение 0.

Примечание.

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

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

Примечание.

Если задача «Выполнение SQL» использует диспетчер соединений ADO или ODBC, свойство Кодовая страница недоступно. Если решению необходимо использовать кодовую страницу, с задачей «Выполнение SQL» следует применять диспетчер соединений OLE DB или ADO.NET.

Режим преобразования типов
Когда этому свойству задается значение Разрешено, задача "Выполнение SQL" пытается преобразовать выходной параметр и результаты запроса в тип данных переменной, к которой относятся эти результаты. Это относится к типу результирующего набора Одна строка .

ResultSet
Укажите ожидаемый тип результата выполнения инструкции SQL. Выберите из Одна строка, Полный результирующий набор, XMLили Нет.

ConnectionType
Выберите тип, используемый диспетчером соединений для соединения с источником данных. В качестве возможных типов соединения могут быть: OLE DB, ODBC, ADO, ADO.NET и SQLMOBILE.

Связанные разделы:OLE DB диспетчер подключений, ODBC диспетчер подключений, ADO диспетчер подключений, ADO.NET диспетчер подключений, SQL Server Compact Edition диспетчер подключений

Соединение
Выберите соединение из списка определенных диспетчеров соединений. Для создания нового подключения выберите <Новое подключение...>.

SQLSourceType
Выберите тип источника для инструкции SQL, выполняемой этой задачей.

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

Это свойство имеет параметры, указанные в следующей таблице.

Значение	Описание
Прямой ввод	Задайте источник для инструкции Transact-SQL. При выборе этого значения отображается динамический параметр SQLStatement.
Соединение с файлом	Выберите файл, содержащий инструкцию Transact-SQL. При установке этого параметра отображается динамический параметр Подключение файла.
Переменная	В качестве источника задайте переменную, определяющую инструкцию Transact-SQL. При выборе этого значения отображается динамический параметр SourceVariable.

QueryIsStoredProcedure
Указывает, является ли заданная для запуска инструкция SQL хранимой процедурой. Если задача использует диспетчер соединений ADO, это свойство доступно только для чтения и записи. В противном случае свойство доступно только для чтения и имеет значение false.

BypassPrepare
Укажите, является ли SQL-запрос подготовленным. true ― пропустить подготовку; false ― подготовить инструкцию SQL перед выполнением. Этот параметр доступен только с соединениями OLE DB, поддерживающими подготовку.

Связанные разделы:Предварительно подготовленное выполнение

Просмотреть
Укажите расположение файла, содержащего инструкцию SQL, при помощи диалогового окна Открыть . Выберите файл, содержимое которого копируется как инструкция SQL в свойство SQLStatement .

Создать запрос
Создайте инструкцию SQL с помощью диалогового окна построителя запросов, графического средства, используемого для создания запросов. Этот параметр доступен, если параметр SQLSourceType установлен в значение Прямой ввод.

Анализ запроса
Проверьте синтаксис инструкции SQL.

Динамические параметры SQLSourceType

SQLSourceType = Прямой ввод

SQLStatement
Введите инструкцию SQL, которую требуется выполнить, в поле параметра или нажмите кнопку обзора (...) для ввода инструкции SQL в диалоговом окне Ввод SQL-запроса, либо нажмите Построить запрос, чтобы составить инструкцию с помощью диалогового окна Построитель запросов.

Связанные разделы:построитель запросов

SQLSourceType = файловое подключение

FileConnection
Выберите существующий диспетчер подключения файлов или нажмите <Новое подключение...>, чтобы создать новый диспетчер подключений.

Связанные разделы:диспетчер подключений файлов, редактор диспетчер подключений файлов

SQLSourceType = Переменная

SourceVariable
Выберите существующую переменную или нажмите <Новая переменная...>, чтобы создать новую переменную.

См. также:Переменные служб Integration Services (SSIS), Добавление переменной

Страница "Сопоставление параметров" — редактор задачи "Выполнение SQL"

Используйте страницу Сопоставление параметров диалогового окна Редактор задачи «Выполнение SQL» для сопоставления переменных с параметрами в инструкции SQL.

Параметры

Имя переменной
После добавления сопоставления параметров нажатием Добавить выберите системную или определяемую пользователем переменную в списке или нажмите <Новая переменная...>, чтобы добавить новую переменную в диалоговом окне Добавление переменной.

Связанные разделы:Переменные служб Integration Services (SSIS)

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

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

Имя параметра
Введите имя параметра.

В зависимости от типа диспетчера соединений, используемого задачей, необходимо использовать числа или имена параметра. Некоторые типы диспетчеров соединений требуют, чтобы первым символом имени параметра был знак @, либо определенные номера, как @Param1, или имена столбцов как имена параметров.

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

Эта настройка гарантирует, что поставщик выделит достаточное пространство для значений параметров изменяемой длины.

Прибавить
Щелкните, чтобы добавить сопоставление параметров.

Удалить
Выберите сопоставление параметра из списка, затем нажмите Удалить.

Страница "Результирующий набор" — редактор задачи "Выполнение SQL"

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

Параметры

Имя результата
После того как вы, нажав Добавить, добавите набор сопоставлений результирующего набора, задайте имя результата. В зависимости от типа результирующего набора вы должны использовать определенные имена результатов.

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

Если результирующий набор имеет тип Полный результирующий набор или XML, то в качестве имени результирующего набора необходимо использовать 0.

Имя переменной
Сопоставьте результирующий набор с переменной, выбрав переменную или нажав <Новая переменная...>, чтобы добавить новую переменную в диалоговом окне Добавить переменную.

Прибавить
Нажмите, чтобы добавить сопоставление набора результатов.

Удалить
Выберите в списке сопоставление набора результатов, а затем нажмите Удалить.

Параметры в задаче "Выполнение SQL"

В инструкциях SQL и хранимых процедурах часто используются входные параметры input , выходные параметры output и коды возврата. В службах Integration Services задача Execute SQL поддерживает типы параметров Input, Output и ReturnValue . Используйте тип Input для входных параметров, Output — для выходных и ReturnValue — для кодов возврата.

Примечание.

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

Параметры команд SQL, включая запросы и хранимые процедуры, сопоставлены с пользовательскими переменными, созданными в области задачи «Выполнение SQL», в области родительского контейнера или в области пакета. Значения переменных можно задать во время разработки или динамически заполнить во время выполнения. Также можно сопоставить параметры системным переменным. Дополнительные сведения см. в разделах Переменные служб Integration Services (SSIS) и Системные переменные.

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

• Применение имен и маркеров параметров

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

• Использование параметров в предложениях WHERE

• Использование параметров с хранимыми процедурами

• Получение значений кодов возврата

Имена параметров и маркеры

В зависимости от типа соединения, который использует задача «Выполнение SQL», синтаксис команды SQL использует различные маркеры параметров. Например, для типа диспетчера подключений ADO.NET необходимо использовать в команде SQL маркер параметра в формате @varParameter, а для типа подключения OLE DB в качестве маркера параметра должен использоваться символ вопросительного знака (?).

Имена, которые можно использовать как имена параметров в сопоставлениях между переменными и параметрами, также зависят от типа диспетчера соединений. Например, для типа диспетчера подключений ADO.NET используется заданное пользователем имя с префиксом @, тогда как для типа диспетчера подключений OLE DB в качестве имени параметра требуется использовать числовое значение порядкового номера с отсчётом от 0.

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

Тип подключения	Маркер параметра	Наименование параметра	Пример команды SQL
ADO	?	Param1, Param2, …	SELECT FirstName, LastName, Title FROM Person.Contact WHERE ContactID = ? -- Этот SQL-запрос выбирает имя, фамилию и должность контакта по идентификатору контакта
ADO.NET	@<имя параметра>	@<имя параметра>	SELECT FirstName, LastName, Title FROM Person.Contact WHERE ContactID = @parmContactID
ODBC	?	1, 2, 3, ...	SELECT FirstName, LastName, Title FROM Person.Contact WHERE ContactID = ? -- Этот SQL-запрос выбирает имя, фамилию и должность контакта по идентификатору контакта
EXCEL и OLE DB	?	0, 1, 2, 3, ...	SELECT FirstName, LastName, Title FROM Person.Contact WHERE ContactID = ? -- Этот SQL-запрос выбирает имя, фамилию и должность контакта по идентификатору контакта

Использование параметров с ADO.NET и диспетчерами подключений ADO

ADO.NET и диспетчеры подключений ADO имеют определенные требования для команд SQL, использующих параметры:

• ADO.NET диспетчеры подключений требуют, чтобы команда SQL использовала имена параметров в качестве маркеров параметров. Это означает, что переменные могут быть прямо сопоставлены с параметрами. Например, переменная @varName сопоставляется с параметром по имени @parName и предоставляет значение параметру @parName.

• Диспетчеры соединений ADO требуют, чтобы в команде SQL в качестве маркеров параметров использовались вопросительные знаки (?). Однако в качестве имен параметров можно использовать любые имена, за исключением целых чисел.

Чтобы предоставить параметрам значения, переменные сопоставляются с именами параметров. Затем задача «Выполнение SQL» использует порядковый номер имени параметра в списке параметров для загрузки значений из переменных в параметры.

Использование параметров с диспетчерами подключений EXCEL, ODBC и OLE DB

Диспетчеры соединений EXCEL, ODBC и OLE DB требуют, чтобы команды SQL использовали символы знака вопроса (?) в качестве маркеров параметров и числовые значения (начиная с нуля или с единицы) в качестве имен параметров. Если задача «Выполнение SQL» использует диспетчер соединений ODBC, то именем параметра, сопоставляемым первому параметру в запросе, является 1. В противном случае именем параметра будет 0. Для последующих параметров числовое значение имени параметра указывает на параметр в команде SQL, которому сопоставлено это имя параметра. Например, параметр под именем 3 сопоставлен с третьим параметром, который представляется третьим знаком вопроса (?) в команде SQL.

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

В зависимости от поставщика, который используют диспетчеры соединений, некоторые типы данных OLE DB могут не поддерживаться. Например, драйвер Excel распознает только ограниченный набор типов данных. Дополнительные сведения о поведении поставщика Jet с драйвером Excel см. в разделе Excel Source.

Использование параметров с диспетчерами подключений OLE DB

Если в задаче "Выполнение SQL" используется диспетчер соединений OLE DB, становится доступным свойство BypassPrepare задачи. Этому свойству необходимо присвоить значение true , если задача «Выполнение SQL» использует инструкции SQL с параметрами.

При использовании диспетчера соединений OLE DB нельзя использовать подзапросы с параметрами, поскольку задача "Выполнение SQL" не может определить сведения о параметрах через поставщик OLE DB. Однако можно использовать выражение, чтобы объединить значения параметров в строку запроса и задать свойство SqlStatementSource этой задачи.

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

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

При чтении данных типов SQL Server, time и datetimeoffset задача Execute SQL, использующая диспетчер соединений ADO.NET или ADO, имеет следующие дополнительные требования:

• Для данных типа time диспетчер соединений ADO.NET требует, чтобы эти данные сохранялись в параметре, тип которого — Input или Output, а тип данных — string.

• Для данных datetimeoffset диспетчер соединений ADO.NET требует, чтобы эти данные хранились в одном из следующих параметров:

  • Параметр типа Input имеет тип данных string.

  • Параметр типа Output или ReturnValueимеет тип данных datetimeoffset, stringили datetime2. Если выбрать параметр, тип данных которого — string или datetime2, службы Integration Services преобразуют данные в тип string или datetime2.

• Диспетчер соединений ADO требует, чтобы данные time или datetimeoffset хранились в параметре типа Input или Output, имеющем тип данных adVarWchar.

Дополнительные сведения о типах данных SQL Server и их сопоставлении с типами данных служб Integration Services см. в разделе Типы данных (Transact-SQL) и Типы данных служб Integration Services.

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

При использовании диспетчера соединений OLE DB задача Execute SQL предъявляет определенные требования к хранению данных типов SQL Server date, time, datetime, datetime2 и datetimeoffset. Эти данные необходимо хранить в одном из параметров следующих типов:

• Входной параметр типа данных NVARCHAR.

• Выходной параметр соответствующего типа данных, как показано в следующей таблице.

  Тип параметраOutput	Тип данных «дата»
  DBDATE	дата
  DBTIME2	time
  DBTIMESTAMP	datetime, datetime2
  DBTIMESTAMPOFFSET	datetimeoffset

Если данные не хранятся в соответствующем входном или выходном параметре, выполнение пакета завершается с ошибкой.

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

При использовании диспетчера соединений ODBC задача Execute SQL имеет определённые требования к хранению данных одного из следующих типов данных SQL Server: date, time, datetime, datetime2 или datetimeoffset. Вы должны хранить эти данные в одном из параметров следующих типов:

• Входной параметр input типа данных SQL_WVARCHAR.

• Параметр output с соответствующим типом данных, как указано в следующей таблице.

  Тип параметраOutput	Тип данных «date»
  SQL_DATE	дата
  SQL_SS_TIME2	time
  SQL_TYPE_TIMESTAMP –или– SQL_TIMESTAMP	datetime, datetime2
  SQL_SS_TIMESTAMPOFFSET	datetimeoffset

Если данные не хранятся в соответствующем входном или выходном параметре, выполнение пакета завершается с ошибкой.

Использование параметров в условиях WHERE

Команды SELECT, INSERTUPDATEи DELETE команды часто включают предложения WHERE для указания фильтров, определяющих условия каждой строки в исходных таблицах, чтобы соответствовать команде SQL. Параметры предоставляют значения фильтра в предложениях WHERE.

Можно использовать маркеры параметров для динамического предоставления значений параметрам. Правила использования маркеров параметров и имен параметров в SQL-выражении зависят от типа менеджера соединений, который использует задача «Выполнение SQL».

В следующей таблице приведен список примеров команды SELECT для разных типов диспетчеров соединений. Операторы INSERT, UPDATE и DELETE аналогичны. В примерах инструкции SELECT возвращают из таблицы Product базы данных AdventureWorks2025 продукты, для которых значение ProductID больше и меньше значений, указанных двумя параметрами.

Тип подключения	Синтаксис SELECT
EXCEL, ODBC и OLEDB	SELECT* FROM Production.Product WHERE ProductId > ? AND ProductID < ?
ADO	SELECT* FROM Production.Product WHERE ProductId > ? AND ProductID < ?
ADO.NET	SELECT* FROM Production.Product WHERE ProductId > @parmMinProductID AND ProductID < @parmMaxProductID

Для примеров необходимы параметры со следующими именами.

• Диспетчеры соединений EXCEL и OLED DB используют параметры с именами 0 и 1. Тип подключения ODBC использует 1 и 2.

• Для типа соединения ADO можно использовать любые два имени параметра, такие как Param1 и Param2, но эти параметры должны быть сопоставлены со своими порядковыми номерами в списке параметров.

• Для типа подключения ADO.NET используются имена параметров @parmMinProductID и @parmMaxProductID.

Использование параметров в хранимых процедурах

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

В следующей таблице приведен список примеров команды EXEC для разных типов диспетчеров соединений. Примеры выполняют хранимую процедуру uspGetBillOfMaterials в базе данных AdventureWorks2025. Хранимая процедура использует параметры @StartProductID и @CheckDateinput.

Тип подключения	Синтаксис EXEC
EXCEL и OLEDB	EXEC uspGetBillOfMaterials ?, ?
ODBC	{call uspGetBillOfMaterials(?, ?)} Дополнительные сведения о синтаксисе вызова ODBC см. в разделе Параметры процедурСправочника по программированию ODBC в библиотеке MSDN.
ADO	Если для параметра IsQueryStoredProcedure задано значение False, EXEC uspGetBillOfMaterials ?, ? Если для параметра IsQueryStoredProcedure задано значение True, uspGetBillOfMaterials
ADO.NET	Если для параметра IsQueryStoredProcedure задано значение False, EXEC uspGetBillOfMaterials @StartProductID, @CheckDate Если для параметра IsQueryStoredProcedure задано значение True, uspGetBillOfMaterials

Чтобы использовать выходные параметры, синтаксис требует, чтобы ключевое слово OUTPUT следовало за каждым маркером параметров. Например, следующий синтаксис выходного параметра является верным: EXEC myStoredProcedure ? OUTPUT.

Дополнительные сведения об использовании входных и выходных параметров с хранимыми процедурами Transact-SQL см. в разделе EXECUTE (Transact-SQL).

Сопоставление параметров запросов с переменными

Этот раздел описывает использование параметризованной инструкции SQL в задаче "Выполнение SQL" и создание сопоставлений между переменными и параметрами в инструкции SQL.

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

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

3. Перейдите на вкладку Поток управления .

4. Если пакет не включает задачу «Выполнение SQL», добавьте его к потоку управления пакета. Дополнительные сведения см. в разделе Добавление задачи или контейнера в поток управления или удаление их из него.

5. Дважды щелкните задачу «Выполнение SQL».

6. Введите параметризированную команду SQL одним из следующих способов.

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

   • Используйте прямые входные данные, нажмите кнопку "Создать запрос", а затем создайте команду SQL с помощью графических инструментов, которые предоставляет построитель запросов.

   • Используйте подключение к файлу, а затем укажите файл, содержащий команду SQL.

   • Используйте переменную и укажите ссылку на переменную, содержащую команду SQL.

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

   Тип подключения	Маркер параметра
   ADO	?
   ADO.NET и SQLMOBILE	@<имя параметра>
   ODBC	?
   EXCEL и OLE DB	?

   В следующей таблице приведен список примеров команды SELECT для разных типов диспетчеров соединений. Параметры предоставляют значения фильтра в предложениях WHERE. В примерах инструкции SELECT возвращают из таблицы Product базы данных AdventureWorks2025 продукты, для которых значение ProductID больше и меньше значений, указанных двумя параметрами.

   Тип подключения	Синтаксис SELECT
   EXCEL, ODBC и OLEDB	SELECT* FROM Production.Product WHERE ProductId > ? AND ProductID < ?
   ADO	SELECT* FROM Production.Product WHERE ProductId > ? AND ProductID < ?
   ADO.NET	SELECT* FROM Production.Product WHERE ProductId > @parmMinProductID AND ProductID < @parmMaxProductID

7. Щелкните Сопоставление параметров.

8. Чтобы добавить сопоставление параметров, нажмите кнопку Добавить.

9. Введите имя в поле Имя параметра .

   Имена параметров зависят от типа соединения, используемого задачей «Выполнение SQL».

   Тип подключения	Наименование параметра
   ADO	Param1, Param2, …
   ADO.NET и SQLMOBILE	@<имя параметра>
   ODBC	1, 2, 3, ...
   EXCEL и OLE DB	0, 1, 2, 3, ...

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

11. В списке Направление укажите, является ли параметр входом, выходом или возвращаемым значением.

12. В списке Тип данных укажите тип данных параметра.

    Внимание

    Тип данных параметра должен быть совместим с типом данных переменной.

13. Повторите шаги с 8 по 11 для каждого параметра инструкции SQL.

    Внимание

    Порядок сопоставления параметров должен соответствовать порядку, в котором параметры появляются в инструкции SQL.

14. Щелкните OK.

Получите значения кодов возврата

Хранимая процедура может возвращать целочисленное значение, называемое кодом возврата, чтобы указать состояние выполнения процедуры. Чтобы реализовать коды возврата в задаче «Выполнение SQL», используйте параметры типа ReturnValue .

В следующей таблице приведен список типов соединений с примерами команды EXEC, которая реализует коды возврата. Все примеры используют входной параметр input . Правила использования маркеров параметров и имен параметров одинаковы для всех типов параметров: Input, Output и ReturnValue.

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

Тип подключения	Синтаксис EXEC
EXCEL и OLEDB	EXEC ? = myStoredProcedure 1
ODBC	{? = call myStoredProcedure(1)} Дополнительные сведения о синтаксисе вызова ODBC см. в разделе Параметры процедурСправочника по программированию ODBC в библиотеке MSDN.
ADO	Если для параметра IsQueryStoreProcedure задано значение False, EXEC ? = myStoredProcedure 1 Если для параметра IsQueryStoreProcedure задано значение True, myStoredProcedure
ADO.NET	Для параметра IsQueryStoreProcedure задано значение True. myStoredProcedure

В описании синтаксиса, показанном в предыдущей таблице, задача «Выполнение SQL» использует для запуска хранимой процедуры тип источника Прямой ввод . Задача «Выполнение SQL» может также пользоваться для выполнения хранимой процедуры типом источника Соединение с файлом . Независимо от того, какой тип источника использует задача «Выполнение SQL» — Прямой ввод или Соединение с файлом , для реализации кода возврата используйте параметр типа ReturnValue .

Дополнительные сведения об использовании кодов возврата с хранимыми процедурами Transact-SQL см. в разделе RETURN (Transact-SQL).

Наборы результатов в задаче "Выполнение инструкции SQL"

В пакете служб Integration Services, возвращается ли результирующий набор задаче Execute SQL, зависит от типа команды SQL, используемой задачей. Например, инструкция SELECT обычно возвращает результирующий набор, а инструкция INSERT — нет.

Содержимое результирующего набора также различается в зависимости от команды SQL. Например, результирующий набор инструкции SELECT может не содержать ни одной строки, содержать одну строку или несколько строк. Тем не менее результирующий набор оператора SELECT, возвращающего количество или сумму, содержит только одну строку.

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

• Указание типа результирующего набора

• Заполнение переменной из результирующего набора

Укажите тип результирующего набора

Задание "Выполнение SQL" поддерживает следующие типы наборов результатов:

• Если запрос не возвращает результатов, используется результирующий набор Нет . Например, этот результирующий набор используется для запросов, которые добавляют, изменяют или удаляют записи в таблице.

• Если запрос возвращает только одну строку, используется результирующий набор Единственная строка . Например, этот результирующий набор используется для оператора SELECT, возвращающего количество или сумму.

• Набор результатов Полный набор результатов используется, когда запрос возвращает несколько строк. Этот результирующий набор используется, например, для инструкции SELECT, получающей все строки таблицы.

• Если запрос возвращает результат в формате XML, используется результирующий набор XML . Этот результирующий набор используется, например, для инструкции SELECT, содержащей предложение FOR XML.

Если в задаче «Выполнение SQL» используется результирующий набор Полный результирующий набор и запрос возвращает несколько наборов строк, задача вернет только первый набор строк. Если этот набор строк формирует ошибку, задача сообщает об ошибке. Если другие наборы строк выдают ошибки, задача не сообщает о них.

Заполнить переменную набором результатов

Результирующий набор, возвращаемый запросом, можно связать с определяемой пользователем переменной, если он содержит одну строку, набор строк или данные в формате XML.

Если тип результирующего набора — Одна строка, можно связать столбец в возвращаемом результирующем наборе с переменной, используя имя столбца в качестве имени результирующего набора, либо использовать порядковый номер столбца в списке столбцов в качестве имени результирующего набора. Например, именем результирующего набора в запросе SELECT Color FROM Production.Product WHERE ProductID = ? может быть Color или 0. Если запрос возвращает несколько столбцов и необходимо получить доступ к значениям во всех столбцах, необходимо каждый столбец связать с отдельной переменной. Если столбцы сопоставляются с переменными с помощью чисел в качестве имени результирующего набора, эти числа отражают порядок, в котором столбцы расположены в списке столбцов запроса. Например, в запросе SELECT Color, ListPrice, FROM Production.Product WHERE ProductID = ?0 используется для столбца Color и 1 — для столбца ListPrice . Возможность использовать имя столбца в качестве имени результирующего набора зависит от поставщика, для работы с которым настроена задача. Не все поставщики предоставляют имена столбцов.

Некоторые запросы, которые возвращают одно значение, могут не включать имена столбцов. Например, инструкция SELECT COUNT (*) FROM Production.Product не возвращает имя столбца. Можно получить доступ к возвращаемому результату, используя порядковый номер позиции 0 в качестве имени результата. Чтобы получить доступ к результату по имени столбца, запрос должен включать конструкцию AS <псевдоним>, чтобы задать имя столбца. Инструкция SELECT COUNT (*)AS CountOfProduct FROM Production.Productпредоставляет столбец CountOfProduct . Затем можно получить доступ к столбцу возвращенного результата, используя имя столбца CountOfProduct или порядковый номер позиции 0.

Если результирующий набор имеет тип Полный результирующий набор или XML, то в качестве имени результирующего набора необходимо использовать 0.

При сопоставлении переменной результирующему набору типа Единственная строка тип переменной должен быть совместим с типом данных столбца, содержащегося в результирующем наборе. Например, если результирующий набор содержит столбец с типом данных String , его нельзя сопоставить переменной с числовым типом данных. Когда свойству TypeConversionMode задается значение Allowed, задача «Выполнение SQL» пытается преобразовать выходной параметр и результаты запроса в тип данных переменной, к которой относятся эти результаты.

Набор результатов XML может быть сопоставлен только с переменной с типом данных String или Object. Если переменная имеет тип данных String , задача «Выполнение SQL» возвращает строковое значение и источник XML может использовать XML-данные. Если переменная имеет тип Object , задача "Выполнение SQL" возвращает DOM-объект.

Полный результирующий набор должен быть сопоставлен с переменной типа данных Object. Возвращаемый результат является объектом набора строк. Можно использовать контейнер Foreach Loop, чтобы извлечь значения из строк таблицы, хранящиеся в переменной Object, в переменные пакета, а затем с помощью задачи «Скрипт» записать в файл данные, хранящиеся в переменных пакета. Демонстрация того, как это сделать с помощью контейнера «Цикл Foreach» и задачи «Сценарий».

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

Тип результирующего набора	Тип данных переменной	Тип объекта
Единственная строка	Любой тип, который совместим с типом столбца в результирующем наборе.	Неприменимо
Полный набор результатов	Объект	Если задача использует собственный диспетчер соединений, в том числе диспетчеры соединений ADO, OLE DB, Excel и ODBC, возвращается объект RecordsetADO. Если задача использует диспетчер управляемых соединений, например диспетчер соединений ADO.NET, возвращается объект System.Data.DataSet. Задачу «Скрипт» можно использовать для доступа к объекту System.Data.DataSet , как показано в следующем примере. Dim dt As Data.DataTable Dim ds As Data.DataSet = CType(Dts.Variables("Recordset").Value, DataSet) dt = ds.Tables(0)
XML	String	String
XML	Объект	Если задача использует собственный диспетчер соединений, в том числе диспетчеры соединений ADO, OLE DB, Excel и ODBC, то возвращается объект MSXML6.IXMLDOMDocument. Если задача использует диспетчер управляемых соединений, например диспетчер соединений ADO.NET, возвращенный объект является System.Xml.XmlDocument.

Переменную можно определить в области задачи «Выполнение SQL» или пакета. Если переменная определена в области пакета, результирующий набор доступен другим задачам и контейнерам внутри пакета, а так же любым пакетам, запущенным задачами «Выполнение пакета» или «Выполнение пакета служб DTS 2000».

При сопоставлении переменной с результирующим набором типа Единственная строка нестроковые значения, возвращенные инструкцией SQL, могут быть преобразованы в строки при выполнении следующих условий:

• Свойство TypeConversionMode имеет значение «true». Значение свойства задается в окне «Свойства» или с помощью редактора задачи «Выполнение SQL».

• Преобразование не приведет к усечению данных.

Сопоставить наборы результатов с переменными в задаче «Выполнение SQL-запроса»

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

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

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

3. Перейдите на вкладку Поток управления .

4. Если пакет не включает задачу «Выполнение SQL», добавьте его к потоку управления пакета. Дополнительные сведения см. в разделе Добавление задачи или контейнера в поток управления или удаление их из него.

5. Дважды щелкните задачу «Выполнение SQL».

6. В диалоговом окне Редактор задачи «Выполнение SQL» на странице Общие выберите в качестве типа результирующего набора Одиночная строка, Полный результирующий наборили XML .

7. Щелкните Набор результатов.

8. Чтобы добавить сопоставление результирующего набора, щелкните Добавить.

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

10. В списке Имя результата при необходимости измените имя результирующего набора.

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

11. Щелкните OK.

Устранение неполадок, связанных с задачей "Выполнение SQL"

Вызовы, сделанные задачей «Выполнение SQL» к внешним источникам данных, можно записывать в журнал. Эта новая возможность протоколирования может быть использована для устранения неполадок, связанных с командами SQL, которые выполняются задачей «Выполнение SQL». Чтобы протоколировать вызовы, которые задача «Выполнение SQL» формирует к внешнему поставщику данных, необходимо разрешить ведение журнала пакета и выбрать событие Диагностика на уровне пакета. Дополнительные сведения см. в разделе Инструменты устранения неполадок при выполнении пакетов.

Иногда команда SQL или хранимая процедура возвращает несколько результирующих наборов. Эти результирующие наборы включают не только наборы строк, которые являются результатом запросов SELECT , но и отдельные значения, являющиеся результатом ошибок инструкций RAISERROR или PRINT . От типа используемого диспетчера соединений зависит, пропускает ли задача ошибки в результирующих наборах, следующих за первым.

• Если используются диспетчеры соединений OLE DB и ADO, задача пропускает результирующие наборы, следующие за первым результирующим набором. Поэтому при использовании этих диспетчеров соединений задача игнорирует ошибку, возвращаемую командой SQL или хранимой процедурой, если ошибка не входит в первый результирующий набор.

• Если используются диспетчеры соединений ODBC и ADO.NET, задача не пропускает результирующие наборы, следующие за первым результирующим набором. Задача с этими диспетчерами соединений завершается ошибкой, если один из результирующих наборов, следующих за первым, содержит ошибку.

Пользовательские записи журнала

В следующей таблице перечислены пользовательские записи журнала для задачи «Выполнение SQL». Дополнительные сведения см. в статье Ведение журналов в службах Integration Services (SSIS).

Запись журнала	Описание
ExecuteSQLExecutingQuery	Предоставляет сведения об этапах выполнения инструкции SQL. Записи журнала формируются в тот момент, когда задача устанавливает соединение с базой данных, когда задача приступает к подготовке инструкции SQL, и после того, как завершается выполнение инструкции SQL. Запись журнала для этапа подготовки содержит инструкцию SQL, которая используется задачей.