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

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

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

• усечение таблицы или представления в процессе подготовки для вставки данных;

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

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

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

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

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

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

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

Connection type	Диспетчер соединений
ХОЛСТ	Диспетчер подключений Excel
OLE DB	Диспетчер подключений OLE DB
ODBC	Диспетчер подключений ODBC
ADO	Диспетчер подключений объектов данных ActiveX
ADO.NET	Диспетчер подключений ADO.NET
SQLMOBILE	Диспетчер подключений SQL Server Compact Edition

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

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

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

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

Примечание.

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

Примечание.

Задача «Выполнение SQL» использует значение перечисления RecognizeAll ParseMode. Дополнительные сведения см. в разделе Пространство имен 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.

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

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

Примечание.

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

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

Примечание.

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

TypeConversionMode
Когда этому свойству задается значение Разрешено, задача "Выполнение 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

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

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

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

WQLQuerySource = Подключение файла

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».

Connection type	Маркер параметра	Наименование параметра	Пример команды SQL
ADO	?	Param1, Param2, …	SELECT FirstName, LastName, Title FROM Person.Contact WHERE ContactID = ?
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 = ?
EXCEL и OLE DB	?	0, 1, 2, 3, ...	SELECT FirstName, LastName, Title FROM Person.Contact WHERE ContactID = ?

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

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, имеет следующие дополнительные требования:

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

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

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

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

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

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

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

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

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

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

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

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

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

При использовании диспетчера соединений ODBC задача Execute SQL имеет определенные требования к хранилищу данных с одним из типов данных SQL Server, даты, времени, даты, даты и времени, даты и времени или datetimeoffset. Эти данные необходимо хранить в параметре одного из следующих типов.

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

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

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

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

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

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

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

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

Connection type	Синтаксис 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 в базе данных AdventureWorks2022. Хранимая процедура использует параметры @StartProductID и @CheckDate input.

Connection type	Синтаксис 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».

   Connection type	Маркер параметра
   ADO	?
   ADO.NET и SQLMOBILE	@<имя параметра>
   ODBC	?
   EXCEL и OLE DB	?

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

   Connection type	Синтаксис 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».

   Connection type	Наименование параметра
   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.

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

Connection type	Синтаксис 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 . Возвращаемый результат является объектом набора строк. Можно использовать контейнер «цикл по каждому элементу», чтобы извлечь значения строк таблицы, которые хранятся в переменной Object, в переменные пакета, а затем с помощью задачи «Сценарий» записать данные, хранящиеся в переменных пакетов, в файл. Реализацию этой задачи с помощью контейнера «цикл по каждому элементу» и задачи «Скрипт» см. далее.

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

Тип результирующего набора	Тип данных переменной	Тип объекта
Единственная строка	Любой тип, который совместим с типом столбца в результирующем наборе.	Нет данных
Полный результирующий набор	Объект	Если задача использует собственный диспетчер соединений, в том числе диспетчеры соединений 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).

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