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

Результирующие наборы в задаче "Выполнение 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 <имя псевдонима> для назначения имени столбца. Инструкция 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, в переменные пакета, а затем с помощью задачи «Сценарий» записать данные, хранящиеся в переменных пакетов, в файл. Демонстрацию этой процедуры с использованием контейнера «цикл по каждому элементу» и задачи «Сценарий» см. в примере CodePlex Execute SQL Parameters and Result Sets (Параметры задачи «Выполнение SQL» и наборы результатов)на веб-сайте msftisprodsamples.codeplex.com.

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

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

Если задача использует управляемый диспетчер соединений, например диспетчер соединений ADO.NET, возвращаемым объектом является System.Xml.XmlDocument.

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

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

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

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

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

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

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

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

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

См. также