Задача «Выполнение SQL»
Изменения: 15 сентября 2007 г.
Задача «Выполнение SQL» выполняет инструкции SQL или хранимые процедуры из пакета. Задача может содержать одну инструкцию SQL или несколько инструкций, запускаемых последовательно. Задача «Выполнение SQL» может быть использована для следующих целей:
- усечение таблицы или представления в процессе подготовки для вставки данных;
- создание, изменение и удаление объектов базы данных, таких как таблицы и представления;
- повторное создание таблиц фактов и таблиц измерений перед загрузкой данных;
- выполнение хранимых процедур;
- сохранение набора строк, возвращенного в переменную из запроса.
Настроить задачу «Выполнение SQL» можно одним из следующих способов:
- Указать тип диспетчера соединений для подключения к базе данных.
- Указать тип результирующего набора, возвращаемого инструкцией SQL.
- Указать время ожидания для инструкции SQL.
- Указать источник для инструкции SQL.
- Указать, должна ли задача пропустить фазу подготовки инструкции SQL.
- При использовании типа соединения ADO необходимо указать, является ли инструкция SQL хранимой процедурой. Для других типов соединений это свойство доступно только для чтения и всегда имеет значение false.
Задача «Выполнение SQL» может использоваться в сочетании с контейнерами «цикл по каждому элементу» и «цикл по элементам» для выполнения нескольких инструкций SQL. Эти контейнеры выполняют повторяющиеся потоки управления в пакете и могут запускать задачу «Выполнение SQL» повторно. Например, с помощью контейнера «цикл по каждому элементу» пакет может перечислять файлы в папке и повторно запускать задачу «Выполнение SQL» с инструкциями SQL из каждого файла.
Подключение к источнику данных
Задача «Выполнение SQL» может использовать разные типы диспетчеров соединений для подключения к источнику данных, где требуется выполнить инструкцию SQL или хранимую процедуру. Задача может использовать типы соединений, перечисленные в следующей таблице.
| Тип соединений | Диспетчер соединений |
|---|---|
EXCEL |
|
OLE DB |
|
ODBC |
|
ADO |
|
ADO.NET |
|
SQLMOBILE |
Создание инструкций SQL
Источником инструкций SQL для этой задачи может быть свойство задачи, которое содержит инструкцию, соединение с файлом, содержащим инструкции, или имя переменной, хранящей инструкцию. Для написания инструкций SQL необходимо использовать разновидность языка SQL, используемую системой управления базой данных-источником (СУБД).
Если инструкции SQL хранятся в файле, задача использует диспетчер подключения файлов для подключения к файлу. Дополнительные сведения см. в разделе Диспетчер подключения файлов.
В конструкторе служб SSIS можно использовать диалоговое окно Редактор задачи «Выполнение SQL» для ввода инструкций SQL или построитель запросов — графический интерфейс пользователя для создания запросов SQL.
.gif "ms141003.note(ru-ru,SQL.90).gif") Примечание. |
|---|
| Задача «Выполнение SQL» не может провести синтаксический анализ допустимых инструкций SQL, созданных за ее пределами. |
Отправка нескольких инструкций в пакете
Если в задачу «Выполнение 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», в области родительского контейнера или в области пакета. Значения переменных могут быть установлены во время проектирования или динамически подставлены во время выполнения. Также можно сопоставить параметры системным переменным. Дополнительные сведения см. в разделах Переменные служб Integration Services и Системные переменные.
В зависимости от типа соединения, который использует задача «Выполнение SQL», синтаксис команды SQL использует различные маркеры параметров. Например, тип диспетчера соединений ADO.NET требует, чтобы команда SQL использовала маркер параметра в формате @varParameter, в то время как тип соединения OLE DB требует символ знака вопроса (?) в качестве маркера параметра.
Имена, которые можно использовать как имена параметров в сопоставлениях между переменными и параметрами, также зависят от типа диспетчера соединений. Например, тип диспетчера соединений ADO.NET использует имена, определенные пользователем, начинающиеся с префикса @, в то время как тип диспетчера соединений OLE DB требует использования числового значения нулевого порядка (т.е. от 0 до 9) в качестве имени параметра.
В следующей таблице подведен итог требованиям для команд SQL для различных типов диспетчеров соединений, которые может использовать задача «Выполнение SQL».
| Тип соединения | Маркер параметра | Имя параметра | Пример команды SQL |
|---|---|---|---|
ADO |
? |
Параметр1, параметр2, … |
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.NET
Диспетчер соединений ADO.NET требует, чтобы команда SQL использовала имена параметров в качестве маркеров параметров. Это означает, что переменные могут быть прямо сопоставлены параметрам. Например, переменная @varName сопоставляется параметру под именем @parName и предоставляет значение параметру @parName.
Параметры диспетчеров соединений EXCEL, ODBC и OLE DB
Диспетчеры соединений EXCEL, ODBC и OLE DB требуют, чтобы команды SQL использовали символы знака вопроса (?) в качестве маркеров параметров и номера (начиная с нуля или с единицы) в качестве имен параметров. Если задача «Выполнение SQL» использует диспетчер соединений ODBC, то именем, сопоставляемым первому параметру в запросе, является 1. В противном случае именем параметра будет 0. Для последовательности параметров числовое значение имени параметра указывает на параметр в команде SQL, которому сопоставлено это имя параметра. Например, параметр под именем 3 сопоставлен с третьим параметром, который представляется третьим знаком вопроса (?) в команде SQL.
Чтобы предоставить параметрам значения, переменные сопоставляются с именами параметров и задача «Выполнение SQL» использует порядковое значение имени параметра для загрузки значения из переменных в параметры.
Если задача «Выполнение» использует тип соединения OLE DB, свойство BypassPrepare задачи становится доступным. Этому свойству необходимо присвоить значение true, если задача «Выполнение SQL» использует инструкции SQL с параметрами.
При использовании диспетчера соединений OLE DB нельзя применять параметризованные вложенные запросы, поскольку в задаче «Выполнение SQL» нельзя получить путем анализа информацию о параметрах через поставщика OLE DB. Однако можно использовать выражение, чтобы объединить значения параметров в строку запроса и задать свойство SqlStatementSource этой задачи.
В зависимости от поставщика, который используют диспетчеры соединений, некоторые типы данных OLE DB могут не поддерживаться. Например, драйвер Excel распознает только ограниченный набор типов данных. Дополнительные сведения о поведении поставщика Jet с драйвером Excel см. в разделе Источник Excel.
Параметры диспетчеров соединений ADO
Диспетчеры соединений ADO требуют, чтобы команды SQL использовали символы знака вопроса (?) в качестве маркеров параметров, но также в качестве имен параметров допустимо использование любых определенных пользователем имен, кроме целых значений.
Чтобы предоставить параметрам значения, переменные сопоставляются с именами параметров и задача «Выполнение SQL» использует порядковое значение имени параметра в списке параметров для загрузки значений из переменных в параметры.
Использование параметров с предложением WHERE
Команды SELECT, INSERT, UPDATE и DELETE часто включают в себя предложение WHERE для задания фильтров, которые определяют условия, которым должна удовлетворять каждая строка в исходной таблице, чтобы попасть под действие команды SQL. Параметры предоставляют значения фильтра в предложениях WHERE.
Можно использовать маркеры параметров для динамического предоставления значений параметрам. Правила для каждого маркера параметра и имени параметра, которые могут быть использованы в инструкции SQL, зависят от типа диспетчера соединений, который используется задачей «Выполнение SQL».
В следующей таблице приведен список примеров команды SELECT для разных типов диспетчеров соединений. Те же самые правила относятся и к инструкциям INSERT, UPDATE и DELETE. Примеры используют SELECT для получения списка продукции из таблицы Product в базе данных AdventureWorks , для которых ProductID больше чем и меньше чем значения, заданные двумя параметрами.
| Тип соединения | Синтаксис SELECT |
|---|---|
EXCEL, ODBC и OLEDB |
|
ADO |
|
ADO.NET |
|
Для примеров требуются параметры со следующими именами.
- Диспетчеры соединений EXCEL и OLED DB используют параметры с именами 0 и 1, а для типа соединения ODBC понадобятся параметры с именами 1 и 2.
- Для типа соединения ADO можно использовать любые два имени параметра, такие как Param1 и Param2, но эти параметры должны быть сопоставлены со своими порядковыми позициями в списке параметров.
- Для типа соединения ADO.NET используются имена параметров @parmMinProductID и @parmMaxProductID.
Использование параметров с хранимыми процедурами
В командах SQL, выполняющих хранимые процедуры, тоже может использоваться сопоставление параметров. Правила о том, как использовать маркеры параметров и имена параметров, зависят от типа диспетчера соединений, который используется задачей «Выполнение SQL», точно так же, как и правила для параметризованных запросов.
В следующей таблице приведен список примеров команды EXEC для разных типов диспетчеров соединений. Примеры выполняют хранимую процедуру uspGetBillOfMaterials в базе данных AdventureWorks. Хранимая процедура использует входные параметры @StartProductID и @CheckDate.
| Тип соединения | Синтаксис EXEC |
|---|---|
EXCEL и OLEDB |
|
Интерфейс ODBC |
Дополнительные сведения о синтаксисе вызова ODBC см. в разделе Параметры процедур справочника по программированию ODBC в библиотеке MSDN. |
ADO |
Если свойство IsQueryStoredProcedure установлено в значение False, Если свойство IsQueryStoredProcedure установлено в значение True, |
ADO.NET |
Если свойство IsQueryStoredProcedure установлено в значение False, Если свойство IsQueryStoredProcedure установлено в значение True, |
Чтобы использовать выходные параметры, синтаксис требует, чтобы ключевое слово OUTPUT следовало за каждым маркером параметров. Например, EXEC myStoredProcedure ? OUTPUT.
Дополнительные сведения об использовании входных и выходных параметров с хранимыми процедурами Transact-SQL см. в разделах Параметры (компонент Database Engine), Возвращение данных с помощью параметров OUTPUT и EXECUTE (Transact-SQL).
Получение значений кодов возврата
Хранимая процедура может возвращать целочисленное значение, называемое кодом возврата, для указания состояния выполнения процедуры. Чтобы реализовать коды возврата в задаче «Выполнение SQL», используйте параметры типа ReturnValue.
В следующей таблице приведен список типов соединений с примерами команды EXEC, которая реализует коды возврата. Все примеры используют входной параметр. Правила использования маркеров параметров и имен параметров с параметрами ReturnValue такие же, как и правила, которые применяются к типам параметров Input и Output.
Некоторые типы синтаксиса не поддерживают литералы параметров. В этом случае необходимо предоставить значение параметра, используя переменную.
| Тип соединения | Синтаксис EXEC |
|---|---|
EXCEL и OLEDB |
|
Интерфейс ODBC |
Дополнительные сведения о синтаксисе вызова ODBC см. в разделе Параметры процедур справочника по программированию ODBC в библиотеке MSDN. |
ADO |
Если свойство IsQueryStoreProcedure установлено в значение False, Если свойство IsQueryStoreProcedure установлено в значение True, |
ADO.NET |
Свойство IsQueryStoreProcedure установлено в значение True.
|
Дополнительные сведения об использовании кодов возврата с хранимыми процедурами Transact-SQL см. в разделах Возврат данных с использованием кода возврата и RETURN (Transact-SQL).
Задание типа результирующего набора
В зависимости от команды SQL задаче «Выполнение SQL» может быть возвращен (или не возвращен) результирующий набор. Например, инструкция SELECT обычно возвращает результирующий набор, а инструкция INSERT нет. Результирующий набор инструкции SELECT может не содержать ни одной строки, содержать одну строку или несколько строк. Хранимые процедуры возвращают целочисленные значения, называемые кодом возврата, который отражает состояние выполнения процедуры. В этом случае результирующий набор состоит из одной строки.
Задача «Выполнение SQL» поддерживает следующие типы результирующих наборов:
- Результирующий набор Нет используется, когда запрос не возвращает результатов. Этот результирующий набор используется, например, для запросов добавления, изменения записей в таблице или их удаления.
- Результирующий набор Одна строка используется, когда запрос возвращает только одну строку. Этот результирующий набор используется, например для хранимых процедур, возвращающих код возврата, или для инструкции SELECT, возвращающей счетчик или сумму.
- Результирующий набор Полный результирующий набор используется, если запрос возвращает несколько строк. Этот результирующий набор используется, например, для инструкции SELECT, получающей все строки таблицы.
- Результирующий набор XML используется, когда запрос возвращает результат в формате XML. Этот результирующий набор используется, например, для инструкции SELECT, содержащей предложение FOR XML.
Если в задаче «Выполнение SQL» используется результирующий набор Полный результирующий набор и запрос возвращает несколько наборов строк, задача вернет только первый набор строк. Если этот набор выдает ошибку, задача оповещает об ошибке. Если другие наборы строк выдают ошибки, задача не фиксирует это.
Задача «Выполнение SQL» преобразует в строки любые значения, возвращенные инструкцией SQL, если они не являются строковыми переменными. Например, значения типов данных SQL Server uniqueidentifier, bigint, decimal или numeric преобразовываются в строковые переменные.
Заполнение переменной из результирующего набора
Результирующий набор, возвращаемый запросом, можно связать с пользовательской переменной, если он содержит одну строку, набор строк или данные в формате 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, его нельзя сопоставить переменной типа numeric. Результирующий набор в формате XML может быть сопоставлен только переменной типа String или Object. Если переменная имеет тип String, задача «Выполнение SQL» возвращает строковое значение, и источник XML может использовать XML-данные. Если переменная имеет тип Object, задача «Выполнение SQL» возвращает DOM-объект. Полный результирующий набор должен быть сопоставлен с типом данных Object. Возвращаемый результат является объектом набора строк. Для перемещения по объекту набора данных и доступа к информации в столбцах, а так же к данным из набора строк, можно написать собственные пользовательские задачи.
В следующей таблице представлены итоговые сведения о типах данных переменных, которые могут быть сопоставлены с результирующими наборами.
| Тип результирующего набора | Тип данных переменной | Тип объекта |
|---|---|---|
Одна строка |
Любой тип, который совместим с типом столбца в результирующем наборе. |
Неприменимо |
Полный результирующий набор |
Object |
Если задача использует собственный диспетчер соединений, в том числе диспетчеры соединений ADO, OLE DB, Excel и ODBC, возвращаемым объектом является Recordset ADO. Если задача использует управляемый диспетчер соединений, такой как ADO.NET, возвращаемым объектом является System.Data.DataSet. |
XML |
String |
String |
XML |
Object |
Если задача использует собственный диспетчер соединений, в том числе диспетчеры соединений ADO, OLE DB, Excel и ODBC, возвращаемым объектом является MSXML6.IXMLDOMDocument. Если задача использует управляемый диспетчер соединений, такой как ADO.NET, возвращаемым объектом является System.Xml.XmlDocument. |
Переменную можно определить в области задачи «Выполнение SQL» или пакета. Если переменная определена в области пакета, результирующий набор доступен другим задачам и контейнерам внутри пакета, а так же любым пакетам, запущенным задачами «Выполнение пакета» или «Выполнение пакета служб DTS 2000».
При сопоставлении переменной с одностроковым результирующим набором значения, возвращенные инструкцией SQL и не являющиеся строками, могут быть преобразованы в строки. От типа используемого диспетчера соединений зависит то, происходит ли это преобразование и является ли оно неявным или явным.
- При использовании диспетчера соединений ADO.NET преобразование не происходит.
- При использовании диспетчера соединений ADO или ODBC это преобразование происходит неявно.
- Диспетчер соединений OLE DB или Excel явно преобразует в строки значения типа DBTYPE_I8, DBTYPE_UI8, DBTYPE_NUMERIC, DBTYPE_GUID и DBTYPE_BYTES.
Дополнительные сведения о загрузке результирующего набора в переменную см. в разделе Как сопоставить результирующие наборы переменным в задаче «Выполнение SQL».
Пользовательские записи журнала, доступные в задаче «Выполнение SQL»
В следующей таблице перечислены пользовательские записи в журнале для задачи «Выполнение SQL». Дополнительные сведения см. в разделах Реализация ведения журналов в пакетах и Пользовательские сообщения для ведения журнала.
| Запись журнала | Описание |
|---|---|
ExecuteSQLExecutingQuery |
Предоставляет сведения об этапах выполнения инструкции SQL. Записи журнала формируются в тот момент, когда задача устанавливает соединение с базой данных, когда задача приступает к подготовке инструкции SQL и после того, как завершается выполнение инструкции SQL. Запись журнала для этапа подготовки содержит инструкцию SQL, которая используется задачей. |
Устранение неполадок, связанных с задачей «Выполнение SQL»
Начиная с Microsoft SQL Server 2005 с пакетом обновления 2 (SP2), появилась возможность для занесения в журнал вызовов, которые задача «Выполнение SQL» делает к внешним поставщикам данных. Эта новая возможность протоколирования может быть использована для устранения неполадок, связанных с командами SQL, которые выполняются задачей «Выполнение SQL». Для протоколирования вызовов, которые задача «Выполнение SQL» совершает к внешнему поставщику данных, необходимо разрешить ведение журнала пакета и выбрать событие Диагностика на уровне пакета. Дополнительные сведения см. в разделе Устранение неполадок выполнения пакетов.
Иногда команда SQL или хранимая процедура возвращает несколько результирующих наборов. Эти результирующие наборы включают не только наборы строк, которые являются результатом запросов SELECT, но и отдельные значения, являющиеся результатом инструкций RAISERROR или PRINT. Кроме диспетчера соединений ODBC, все другие диспетчеры соединений пропускают результирующие наборы, следующие за первым результирующим набором. Поэтому подобные диспетчеры соединений не обрабатывают ошибку, возвращенную командой SQL или хранимой процедурой, если эта ошибка не является частью первого результирующего набора.
Настройка задачи «Выполнение SQL»
Свойства задаются программно или через конструктор служб SSIS.
Дополнительные сведения о свойствах, которые можно задать в конструкторе служб SSIS, см. в следующих разделах:
- Редактор задачи «Выполнение SQL» (страница «Общие»)
- Редактор задачи «Выполнение SQL» (страница «Сопоставление параметров»)
- Редактор задачи «Выполнение SQL» (страница «Результирующий набор»)
- Страница «Выражения»
Дополнительные сведения об установке этих свойств в конструкторе служб SSIS см. в следующем разделе:
Программная настройка задачи «Выполнение SQL»
Дополнительные сведения о программной установке этих свойств см. в следующих разделах:
См. также
Задачи
Как сопоставить параметры запроса переменным в задаче «Выполнение SQL»
Основные понятия
Контейнер «Цикл по каждому элементу»
Контейнер «Цикл по элементам»
Задачи служб Integration Services
Создание пакета потока управления
Другие ресурсы
Справка и поддержка
Получение помощи по SQL Server 2005
Журнал изменений
| Версия | Журнал |
|---|---|
15 сентября 2007 г. |
|
12 декабря 2006 г. |
|
17 июля 2006 г. |
|
14 апреля 2006 г. |
|
5 декабря 2005 г. |
|