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

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

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

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

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

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

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

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

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

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

Если задача Execute SQL использует полный результирующий набор и запрос возвращает несколько наборов строк, задача возвращает только первый набор строк. Если этот набор строк создает ошибку, задача сообщает об ошибке. Если другие наборы строк создают ошибки, задача не сообщает о них.

Заполнение переменной с набором данных из результата

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

Если результирующий набор является одной строкой, можно привязать столбец в результате возврата к переменной, используя имя столбца в качестве имени результирующих наборов или использовать порядковое положение столбца в списке столбцов в качестве имени результирующий набор. Например, имя результирующих наборов для запроса SELECT Color FROM Production.Product WHERE ProductID = ? может быть цветом или 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, не может быть сопоставлен с переменной, имеющей числовой тип данных. При задании свойства AllowedTypeConversionMode задача Execute SQL попытается преобразовать выходной параметр и результаты запроса в тип данных переменной, для которых назначены результаты.

Результирующий набор XML может сопоставляться только с переменной типа данных String или Object. Если переменная имеет String тип данных, задача Execute SQL возвращает строку, а источник XML может использовать XML-данные. Если переменная имеет Object тип данных, задача Execute SQL возвращает объект Объектной модели документа (DOM).

Полный результирующий набор должен сопоставляться с переменной Object типа данных. Результат возврата — это объект набора строк. Контейнер цикла foreach можно использовать для извлечения значений строк таблицы, хранящихся в переменной object, в переменные пакета, а затем использовать задачу скрипта для записи данных, хранящихся в переменных пакетов в файл. Для демонстрации того, как это сделать с помощью контейнера цикла Foreach и задачи скрипта, см. пример на CodePlex, Execute SQL Parameters and Result Sets, на сайте msftisprodsamples.codeplex.com.

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

Тип набора результатов Тип данных переменной Тип объекта
Одна строка Любой тип, совместимый с столбцом типа в результирующем наборе. Неприменимо
Полный набор результатов Object Если задача использует собственный диспетчер соединений, включая диспетчеры подключений ADO, OLE DB, Excel и ODBC, возвращенный объект является ADO Recordset.

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

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

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

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

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

Чтобы узнать, как загрузить результирующий набор в переменную, см. раздел "Сопоставление наборов результатов с переменными в задаче Execute SQL".

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

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

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

Сопоставление наборов результатов с переменными в задаче Execute SQL

Связанные материалы

  • Last updated on