Compartir a través de

  • Artículo

Tipo de recopilador de consultas T-SQL genérico

El tipo de recopilador de consultas T-SQL genérico ejecuta una instrucción Transact-SQL proporcionada por el usuario como parámetro de entrada, guarda el resultado de la consulta y, a continuación, carga el resultado en el almacén de administración de datos. Este tipo de recopilador se registra en la vista core.supported_collector_types en el almacén de administración de datos.

Este recopilador tiene los parámetros de entrada siguientes:

  • Value: la consulta Transact-SQL. Puede proporcionar más de una consulta como entrada.

  • OutputTable- Proporcione el nombre de la tabla en la que se deben guardar los resultados de la consulta antes de cargarlos en el almacén de administración de datos.

  • Database: especifique las bases de datos en las que ejecutar la consulta. Puede especificar las bases de datos por nombre o utilizar el carácter comodín * para especificar todas las bases de datos del servidor. Si no proporciona ningún valor para Database, la consulta se ejecutará en todas las bases de datos del sistema.

Esquema de entrada de consultas T-SQL genérico

El esquema para la entrada del recopilador de consultas T-SQL genérico es como se muestra a continuación.

<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>

Los ejemplos de código siguientes muestran cómo puede utilizarse el esquema. Para obtener un ejemplo de código completo, vea Cómo crear un conjunto de recopilación personalizado que utilice el tipo de recopilador de consultas T-SQL genérico.

Ejemplo 1

En el ejemplo siguiente se consultan las bases de datos de usuario y sistema y, a continuación, los resultados se colocan en la tabla 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>

Ejemplo 2

En el ejemplo siguiente se consultan las tres bases de datos del sistema y, a continuación, los resultados se colocan en la tabla 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>

Ejemplo 3

En el ejemplo siguiente solamente se consultan las bases de datos de usuario y los resultados se colocan en las tablas custom_snapshots.MyOutputTable2 y 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>

Cuándo utilizar elementos de recopilación independientes

En el ejemplo anterior, todas las consultas se ejecutan en el mismo conjunto de bases de datos. Si necesita ejecutar consultas distintas para bases de datos distintas, debe crear un elemento de recopilación independiente para cada combinación de consulta y base de datos.

Otro escenario que requiere elementos de recopilación distintos es el de dos bases de datos con el mismo nombre de tabla, pero distinto esquema. En el ejemplo 4 se muestran los elementos de recopilación para ocuparse de este escenario.

Ejemplo 4

Cree el elemento de recopilación para la primera base de datos, 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>

Cree el elemento de recopilación para la segunda base de datos, 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>

Procesamiento y resultado

Los metadatos del resultado de la consulta dependen de la instrucción Transact-SQL especificada por el usuario, pero las asignaciones de columnas en el origen o destino del flujo de datos se crean en tiempo de diseño. Esto significa que cada elemento de recopilación que utiliza este tipo de recopilador requiere un paquete personalizado generado en tiempo de ejecución y ajustado al formato de su esquema.

El recopilador de datos utiliza una tarea personalizada para crear un paquete de recopilación y un paquete de carga cuando se recibe un nuevo elemento de recopilación, o si hay una actualización en los parámetros de entrada de un elemento de recopilación existente. Estos paquetes se almacenan en msdb para que puedan volver a utilizarse.

La recopilación de datos y el comportamiento de la carga son similares a los que se describen en Arquitectura y procesamiento del recopilador de datos, pero se implementan de forma algo distinta para el tipo de recopilador de consultas T-SQL genérico.

Las siguientes tablas del almacén de administración de datos se utilizan para almacenar los datos recopilados por el tipo de recopilador de consultas T-SQL genérico:

  • core.snapshots: esta tabla identifica cada nueva instantánea.

  • core.snapshot_timetable: la hora de la instantánea se almacena en una tabla independiente, porque pueden producirse muchas instantáneas prácticamente a la vez.

  • core.source.info: esta tabla almacena información sobre el origen de los datos, así como los datos.

Limitaciones

Hay ciertas limitaciones que deben tenerse en cuenta al utilizar el tipo de recopilador de consultas T-SQL genérico:

  • Los nombres de columna siguientes se reservan para el recopilador de datos: snapshot_time, snapshot_id y database_name. Ninguna tabla creada por conjuntos de recopilación personalizados puede utilizar estos nombres. Se devuelve un error si se intenta utilizarlos.

  • Una columna de tipo sysname se convierte en nvarchar(128) cuando se copia en el almacén de administración de datos. Durante la recopilación de datos, SQL Server 2008 Integration Services (SSIS) convierte los tipos de datos de la base de datos en los tipos de datos de SSIS (por ejemplo, sysname se convierte en DT_WSTR y nvarchar(len) en DT_WSTR). Esta conversión tiene lugar dentro de la tarea de flujo de datos de origen OLE DB. Durante la carga de los datos, el recopilador de datos lee los datos de la memoria caché como un tipo de datos de SSIS y los datos se tratan como nvarchar(128), que es funcionalmente equivalente a sysname.

  • Una columna del tipo char(N) se convierte en varchar(N) cuando se copia en el almacén de administración de datos (char(N) cabe en varchar(N)). Excepto para el hecho de que el tamaño de almacenamiento de char es fijo, mientras que el tamaño de almacenamiento de varchar es variable, estos tipos se tratan como funcionalmente equivalentes.

  • Una columna de tipo varbinary se convierte en binary cuando se copia en el almacén de administración de datos.

  • Una columna de tipo decimal se convierte en numeric cuando se copia en el almacén de administración de datos.

  • Una columna de tipo nchar se convierte en nvarchar cuando se copia en el almacén de administración de datos.

  • El tipo sqlvariant se controla mediante el procesamiento predeterminado para las columnas de las que SSIS no se ocupa directamente. Esto significa que las columnas se tratan como nvarchar(255), dejando cualquier conversión al proveedor de datos.

    Nota

    En este caso, la columna se crea de manera predeterminada con una longitud de 255 caracteres. Pero puede cambiarse a 4.000 caracteres.

  • Todas las columnas devueltas por una consulta Transact-SQL deben tener nombre. Por ejemplo, select 1 no funcionará, pero select 1 as one si que funcionará.

  • SSIS no admite los tipos de datos siguientes, y no pueden incluirse como columnas en ninguna tabla de salida generada por un conjunto de recopilación que use el tipo de recopilador de consultas T-SQL genérico:

    • image

    • text

    • ntext

    • XML

  • Todas las consultas ejecutadas por el tipo de recopilador de consultas T-SQL genérico deben devolver un conjunto de resultados único.

  • Se admiten consultas a la tabla temporal local solo si se declaran primero como parte del mismo lote. Se admiten por completo las consultas a la tabla temporal global.

  • Ningún índice, clave privada, clave externa o ninguna otra restricción se traslada a las tablas de destino del almacén de administración de datos. Esto es así porque los mismos datos se consultan varias veces, y los datos pueden ir de varios equipos a una sola tabla.

  • Cualquier otra limitación con respecto al tipo de consultas compatibles aplicables a la tarea de flujo de datos del origen OLE DB en SSIS también se aplica al tipo de recopilador de consultas T-SQL genérico.