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

  • Статья

Тип сборщика «Универсальный запрос T-SQL»

Тип сборщика «Универсальный запрос T-SQL» выполняет пользовательскую инструкцию Transact-SQL, предоставляемую в качестве входного параметра, сохраняет выходные данные запроса, а затем загружает их в хранилище данных управления. Этот тип сборщика зарегистрирован в представлении core.supported_collector_types в хранилище данных управления.

Этот сборщик имеет следующие входные параметры:

  • Value — запрос Transact-SQL. Можно ввести несколько запросов.

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

  • Database — укажите одну или несколько баз данных, для которых будет выполняться запрос. Можно указать базы данных поименно или использовать символ-шаблон *, чтобы указать все базы данных на сервере. Если для параметра Database не задается значение, то запрос выполняется во всех системных базах данных.

Схема входных данных для сборщика «Универсальный запрос T-SQL»

Схема входных данных для типа сборщика «Универсальный запрос T-SQL» выглядит следующим образом.

<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" targetNamespace="DataCollectorType">
  <xs:element name="TSQLQueryCollector">
    <xs:complexType>
      <xs:sequence>
        <xs:element name="Query" minOccurs="1" maxOccurs="unbounded">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="Value" type="xs:string" />
              <xs:element name="OutputTable" type="xs:string" />
            </xs:sequence>
          </xs:complexType>
        </xs:element>
        <xs:element name="Databases" minOccurs="0" maxOccurs="1">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="Database" minOccurs="0" maxOccurs="unbounded" type="xs:string" />
            </xs:sequence>
            <xs:attribute name="UseSystemDatabases" type="xs:boolean" use="optional" />
            <xs:attribute name="UseUserDatabases" type="xs:boolean" use="optional" />
          </xs:complexType>
        </xs:element>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>

Следующие примеры кода демонстрируют, как можно использовать данную схему. Полный образец кода см. в разделе Как создать пользовательский набор сбора, использующий тип сборщика «Универсальный запрос T-SQL».

Пример 1

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

<ns:TSQLQueryCollector xmlns:ns="DataCollectorType">
  <Query>
    <Value>SELECT DB_NAME() as db_name</Value>
    <OutputTable>VerifyDbName</OutputTable>
  </Query>
  <Databases UseSystemDatabases="true" UseUserDatabases="true" /> 
</ns:TSQLQueryCollector>

Пример 2

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

<ns:TSQLQueryCollector xmlns:ns="DataCollectorType">
  <Query>
    <Value>SELECT * FROM sys.objects</Value>
    <OutputTable>MyOutputTable1</OutputTable>
  </Query>
  <Databases>
    <Database>model</Database>
    <Database>tempdb</Database>
    <Database>master</Database>
  </Databases>
</ns:TSQLQueryCollector>

Пример 3

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

<ns:TSQLQueryCollector xmlns:ns="DataCollectorType">
  <Query>
    <Value>SELECT * FROM sys.dm_tran_database_transactions</Value>
    <OutputTable>MyOutputTable2</OutputTable>
  </Query>
  <Query>
    <Value>SELECT * FROM sys.dm_db_file_space_usage</Value>
    <OutputTable>MyOutputTable3</OutputTable>
  </Query>
  <Databases UseSystemDatabases="false" UseUserDatabases="true" />
</ns:TSQLQueryCollector>

Когда использовать отдельные элементы сбора

В предыдущем примере все запросы выполнялись к одному и тому же набору баз данных. Если требуется выполнить несколько разных запросов для нескольких разных баз данных, необходимо создать отдельный элемент сбора для каждого сочетания запрос-база данных.

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

Пример 4

Создание элемента сбора для первой базы данных, db1.

<ns:TSQLQueryCollector xmlns:ns="DataCollectorType">
  <Query>
    <Value>SELECT * FROM mytable</Value> -- Query mytable
    <OutputTable>MyOutputTable1</OutputTable>
  </Query>
  <Databases>
    <Database>db1</Database>
  </Databases>
</ns:TSQLQueryCollector>

Создание элемента сбора для второй базы данных, db2.

<ns:TSQLQueryCollector xmlns:ns="DataCollectorType">
  <Query>
    <Value>SELECT * FROM mytable</Value> -- Query mytable
    <OutputTable>MyOutputTable2</OutputTable>
  </Query>
  <Databases>
    <Database>db2</Database>
  </Databases>
</ns:TSQLQueryCollector>

Обработка и вывод данных

Выходные метаданные запроса зависят от инструкции Transact-SQL, указанной пользователем, но сопоставления столбцов для источника или назначения потока данных создаются в процессе разработки. Это означает, что для каждого элемента сбора, использующего данный тип сборщика, требуется наличие собственного пакета, создаваемого во время выполнения и подогнанного к формату его схемы.

Каждый раз, когда поступает новый элемент сбора или когда обновляются входные параметры существующего элемента сбора, сборщик данных использует пользовательскую задачу, чтобы создать пакет сбора и загрузить его. Эти пакеты сохраняются в базе данных msdb для последующего повторного использования.

Методы сбора и передачи данных аналогичны тем, которые описаны в разделе Архитектура и обработка сборщиков данных, однако для типа сборщика «Универсальный запрос T-SQL» они осуществляются несколько другим способом.

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

  • core.snapshots — таблица, которая определяет имя каждого нового моментального снимка;

  • core.snapshot_timetable — время создания моментального снимка хранится в другой таблице, поскольку множество снимков может быть создано почти в одно и то же время;

  • core.source.info — в этой таблице содержатся не только данные, но и сведения об их источниках.

Ограничения

При использовании типа сборщика «Универсальный запрос T-SQL» следует учитывать следующие ограничения:

  • Для сборщика данных зарезервированы следующие имена столбцов: snapshot_time, snapshot_id и database_name. Эти имена не могут быть использованы ни в одной из таблиц, созданных пользовательскими наборами сбора. При попытке их использования возвращается ошибка.

  • Столбец типа sysname во время копирования в хранилище управляющих данных преобразуется в тип nvarchar(128). Во время сбора данных службы SQL Server 2008 Integration Services (SSIS) преобразуют типы данных базы данных в типы данных служб SSIS (например, sysname преобразуется в DT_WSTR, а nvarchar(len) преобразуется в DT_WSTR). Преобразование осуществляется внутри задачи потока данных источника OLEDB. Во время загрузки данных сборщик данных считывает данные из кэша как тип данных служб SSIS; эти данные обрабатываются как тип данных nvarchar(128), который функционально эквивалентен типу sysname.

  • Столбец типа char(N) во время копирования в хранилище управляющих данных преобразуется в тип varchar(N) (данные типа char(N) можно уместить в тип varchar(N)). Эти типы считаются функционально эквивалентными за исключением того, что размер хранения у типа char фиксированный, а у типа varchar — переменный.

  • Столбец типа varbinary во время копирования в хранилище управляющих данных преобразуется в тип binary.

  • Столбец типа decimal во время копирования в хранилище управляющих данных преобразуется в тип numeric.

  • Столбец типа nchar во время копирования в хранилище управляющих данных преобразуется в тип nvarchar .

  • В отношении типа sqlvariant применяется обработка по умолчанию для столбцов, которые не обрабатываются напрямую службами SSIS. Это означает, что все столбцы обрабатываются как тип nvarchar(255), а все преобразования должен производить поставщик данных.

    ПримечаниеПримечание

    В этом случае столбец по умолчанию создается с длиной в 255 символов. Но длину можно изменить на 4 000 символов.

  • Все столбцы, возвращаемые запросом Transact-SQL, должны иметь имя. Например, инструкция select 1 не будет работать, а инструкция select 1 as one будет.

  • Следующие типы данных не поддерживаются службами SSIS и не могут быть включены в качестве столбцов в любую выходную таблицу, создаваемую набором сбора, который использует тип сборщика «Универсальный запрос T-SQL».

    • image

    • text

    • ntext

    • XML

  • Все запросы, исполняемые типом сборщика «Универсальный запрос T-SQL», должны возвращать единый результирующий набор.

  • Запросы для локальных временных таблиц поддерживаются только в случае, если они были предварительно объявлены как часть того же пакета. Запросы для глобальных временных таблиц поддерживаются в полном объеме.

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

  • Любые другие ограничения по типу поддерживаемых запросов, относящиеся к задаче потока данных источника OLE DB в службах SSIS, также касаются и типа сборщика «Универсальный запрос T-SQL».

Журнал изменений

Обновления

Исправлены схема и все примеры для типа сборщика «Универсальный запрос T-SQL».