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

Статья
09/10/2014

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

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

Чтобы работать с результирующими наборами в задаче «Выполнение SQL», требуется большее, чем просто знать, возвращает ли команда 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. Возвращаемый результат является объектом набора строк. Вы можете использовать контейнер Foreach Loop для извлечения значений строки таблицы, хранящихся в переменной Object, в переменные пакета, а затем использовать задачу скрипта для записи данных из переменных пакета в файл. Демонстрацию того, как это сделать, см. в примере CodePlex Параметры и наборы результатов при выполнении 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»

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

Редактор задачи «Выполнение SQL» (страница «Результирующий набор»)

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

Задание свойств задач или контейнеров

Связанные задачи

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

См. также

Образец CodePlex, Параметры задачи «Выполнение SQL» и результирующие наборы, на сайте msftisprodsamples.codeplex.com

Оставайтесь в курсе последних возможностей служб Integration Services

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

Посетить страницу «Службы Integration Services» на сайте MSDN

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

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