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

  • Статья

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

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

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

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

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

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

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

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

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

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

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

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

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

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

В зависимости от типа диспетчера соединений, значения, возвращаемые инструкцией SQL, которые еще не являются строками, должны быть явно или неявно преобразованы в строки. Диспетчер соединений ADO или ODBC неявно преобразует возвращенные значения в строки. Диспетчер соединений OLE DB явно преобразует в строки значения с типом данных DBTYPE_I8, DBTYPE_UI8, DBTYPE_NUMERIC, DBTYPE_GUID и DBTYPE_BYTES. Однако диспетчер соединений ADO.NET не преобразует возвращенные значения в строки.

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

Результирующий набор, возвращаемый запросом, можно связать с определяемой пользователем переменной, если он содержит одну строку, набор строк или данные в формате 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_NUMERIC, DBTYPE_GUID и DBTYPE_BYTES.

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

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

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

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

Внешние ресурсы

Значок служб Integration Services (маленький) Будьте в курсе новых возможностей служб Integration Services

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

Чтобы получать автоматические уведомления об этих обновлениях, подпишитесь на RSS-каналы, предлагаемые на этой странице.