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

Применимо к: даSQL Server (все поддерживаемые версии) да Azure-SSIS Integration Runtime в Фабрике данных Azure

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

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

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

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

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

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

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

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

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

Тип соединений Диспетчер соединений
EXCEL Диспетчер подключений 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.

В конструкторе служб Integration Services можно использовать диалоговое окно Редактор задачи «Выполнение 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.

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

Страница "Общие" — редактор задачи "Выполнение SQL"

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

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

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

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

Описание
Приведите описание задачи «Выполнение 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.

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

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
Выберите существующий диспетчер подключений файлов или создайте его, щелкнув пункт <New connection...>.

См. также: подробные сведения о диспетчере файловых подключений и о редакторе диспетчера файловых подключений.

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

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

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

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

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

Параметры

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

См. также: Переменные в службах Integration Services (SSIS)

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

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

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

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

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

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

Добавление
Нажмите для добавления сопоставления параметра.

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

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

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

Параметры

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

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

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

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

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

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

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

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

Примечание

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

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

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

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

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

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

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

Тип соединений Маркер параметра Имя параметра Пример команды SQL
ADO ? Param1, Param2, … SELECT FirstName, LastName, Title FROM Person.Contact WHERE ContactID = ?
ADO.NET @<parameter name> @<parameter name> 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, задача "Выполнение SQL", которая использует диспетчер соединений ADO.NET или ADO, имеет следующие дополнительные требования:

  • Диспетчер соединений ADO.NET требует, чтобы данные типа time хранились в параметре типа 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 задача "Выполнение SQL" имеет особые требования по хранению данных типа данных SQL Server : date, time, datetime, datetime2 и datetimeoffset. Эти данные необходимо хранить в параметре одного из следующих типов.

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

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

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

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

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

При использовании диспетчера соединений ODBC задача "Выполнение SQL" имеет особые требования по хранению данных SQL Server одного из следующих типов: date, time, datetime, datetime2 или 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 базы данных AdventureWorks2012 продукты, для которых значение 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 в базе данных AdventureWorks2012. Хранимая процедура использует параметры @StartProductID и @CheckDate input.

Тип соединений Синтаксис 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 @<parameter name>
    ODBC ?
    EXCEL и OLE DB ?

    В следующей таблице приведен список примеров команды SELECT для разных типов диспетчеров соединений. Параметры предоставляют значения фильтра в предложениях WHERE. В примерах инструкции SELECT возвращают из таблицы Product базы данных AdventureWorks2012 продукты, для которых значение 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 @<parameter name>
    ODBC 1, 2, 3, ...
    EXCEL и OLE DB 0, 1, 2, 3, ...
  10. Выберите переменную из списка Имя переменной . Дополнительные сведения см. в разделе Добавление, удаление и изменение области определяемой пользователем переменной в пакете.

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

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

    Важно!

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

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

    Важно!

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

  14. Нажмите кнопку ОК.

Возвращение значений кодов возврата

Хранимая процедура может возвращать целочисленное значение, называемое кодом возврата, чтобы указать состояние выполнения процедуры. Чтобы реализовать коды возврата в задаче «Выполнение 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 возвращение результирующего набора задаче «Выполнение 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 <alias name> для предоставления имени столбца. Инструкция 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, возвращается объект Recordset ADO.

Если задача использует управляемый диспетчер подключений, например 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. Нажмите кнопку ОК.

Устранение неполадок, связанных с задачей "Выполнение 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, которая используется задачей.