Compartilhar via

  • Artigo

Tipo de coletor de Consultas T-SQL Genérico

O tipo de coletor de Consultas T-SQL Genérico executa uma instrução Transact-SQL fornecida pelo usuário como um parâmetro de entrada, salva a saída da consulta e carrega a saída para o data warehouse de gerenciamento. Esse tipo de coletor é registrado na exibição core.supported_collector_types no data warehouse de gerenciamento.

Esse coletor tem os seguintes parâmetros de entrada:

  • Value - A consulta Transact-SQL. Você pode fornecer mais de uma consulta como entrada.

  • OutputTable - Forneça o nome da tabela para salvar os resultados da consulta antes que eles sejam carregados no data warehouse de gerenciamento.

  • Database - Especifique o banco de dados ou bancos de dados nos quais executar a consulta. Você pode especificar bancos de dados por nome ou usar o caractere curinga * para especificar todos os bancos de dados no servidor. Se você não fornecer um valor para Database, a consulta será executada em todos os bancos de dados do sistema.

Esquema de entrada de Consultas T-SQL Genérico

O esquema de entrada do coletor de Consultas T-SQL Genérico é como a seguir.

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

Os códigos de exemplo a seguir mostram como o esquema pode ser usado. Para obter um exemplo de código completo, consulte Como criar um conjunto de coleta personalizado que usa o tipo de coletor de Consultas T-SQL genérico.

Exemplo 1

O exemplo a seguir consulta o sistema e os bancos de dados de usuário e coloca os resultados na tabela 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>

Exemplo 2

O exemplo a seguir consulta os três bancos de dados do sistema e coloca os resultados na tabela 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>

Exemplo 3

O exemplo a seguir consulta apenas os bancos de dados de usuário e coloca os resultados nas tabelas custom_snapshots.MyOutputTable2 e 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>

Quando usar itens de coleta separados

No exemplo anterior, todas as consultas são executadas no mesmo conjunto de bancos de dados. Se você precisar executar consultas distintas para bancos de dados diferentes, crie um item de coleta separado para cada combinação de banco de dados de consulta.

Outro cenário que requer dois itens de coleta diferentes é o de dois bancos de dados com o mesmo nome de tabela, mas com esquemas diferentes. O exemplo 4 mostra os itens de coleta para tratar esse cenário.

Exemplo 4

Crie o item de coleta para o primeiro banco de dados, 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>

Crie o item de coleta para o segundo banco de dados, 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>

Processamento e saída

Os metadados de saída da consulta dependem da instrução Transact-SQL especificada pelo usuário, mas os mapeamentos de coluna na origem ou no destino do fluxo de dados são criados no momento do design. Isso significa que cada item de coleta que usa esse tipo de coletor requer um pacote personalizado gerado no tempo de execução e ajustado ao formato de seu esquema.

O coletor de dados usa uma tarefa personalizada para criar um pacote de coletas e um pacote de carregamentos quando um novo item de coleta é recebido, ou se houver uma atualização nos parâmetros de entrada de um item de coleta existente. Esses pacotes são salvos no msdb de forma que possam ser reutilizados.

O comportamento de coleta e carregamento é semelhante ao descrito em Arquitetura e processamento do coletor de dados, mas com implementações ligeiramente diferentes para o tipo de coletor de Consultas T-SQL Genérico.

As seguintes tabelas do data warehouse de gerenciamento são usadas para armazenar dados coletados pelo tipo de coletor de Consultas T-SQL Genérico:

  • core.snapshots - Esta tabela identifica cada novo instantâneo.

  • core.snapshot_timetable - A hora do instantâneo é armazenada em uma tabela separada, porque muitos instantâneos podem ocorrer quase ao mesmo tempo.

  • core.source.info - Esta tabela armazena informações sobre a fonte de dados assim como os dados.

Limitações

Há determinadas limitações a serem consideradas ao usar o tipo de coletor de Consultas T-SQL Genérico:

  • Os seguintes nomes de coluna são reservados para o coletor de dados: snapshot_time, snapshot_id e database_name. Nenhuma tabela criada por conjuntos de coleta personalizados pode usar esses nomes. Um erro será retornado se uma tentativa for feita para usá-los.

  • Uma coluna de tipo sysname é convertida em nvarchar(128) quando é copiada no data warehouse de gerenciamento. Durante a coleta de dados, o SQL Server 2008 Integration Services (SSIS) converte tipos de dados de banco de dados em tipos de dados SSIS (por exemplo, sysname se torna DT_WSTR e nvarchar(len) se torna DT_WSTR). Essa conversão ocorre dentro da tarefa de fluxo de dados de origem OLEDB. Durante o carregamento dos dados, o coletor de dados lê os dados do cache como um tipo de dados SSIS e os dados são tratados como nvarchar(128) que é funcionalmente equivalente a sysname.

  • Uma coluna do tipo char(N) se torna varchar(N) quando copiada no data warehouse de gerenciamento (char(N) pode ser ajustado em varchar(N)). Exceto pelo fato de que o tamanho do armazenamento de char é fixo, enquanto o tamanho do armazenamento de varchar é variável, esses tipos são tratados como funcionalmente equivalentes.

  • Uma coluna de tipo varbinary se torna em binary quando é copiada no data warehouse de gerenciamento.

  • Uma coluna de tipo decimal se torna numeric quando é copiada no data warehouse de gerenciamento.

  • Uma coluna de tipo nchar se torna nvarchar quando é copiada no data warehouse de gerenciamento.

  • O tipo sqlvariant é controlado pelo processamento padrão de colunas que o SSIS não controla diretamente. Isso significa que as colunas são tratadas como nvarchar(255), deixando qualquer conversão para o provedor de dados.

    ObservaçãoObservação

    Nesse caso, a coluna é criada com um comprimento padrão de 255 caracteres. No entanto, ele pode ser alterado para até 4.000 caracteres.

  • Todas as colunas retornadas por uma consulta Transact-SQL devem ter um nome. Por exemplo, select 1 não funcionará, mas select 1 as one funcionará.

  • Os tipos de dados a seguir não têm suporte no SSIS e não podem ser incluídos como colunas em nenhuma tabela de saída gerada por um conjunto de coleta que usa o tipo de coletor de Consultas T-SQL Genérico:

    • image

    • text

    • ntext

    • XML

  • Todas as consultas executadas pelo tipo de coletor Consultas T-SQL Genérico devem retornar um único conjunto de resultados.

  • Só haverá suporte para consultas de tabela temporárias locais se elas forem declaradas primeiro como parte do mesmo lote. Há suporte total para consultas de tabela temporárias globais.

  • Nenhum índice, chaves privadas, chaves estrangeiras ou qualquer outra restrição são transferidos às tabelas de destino no data warehouse de gerenciamento. Isso porque os mesmos dados são consultados várias vezes, e os dados podem estar vindo de vários computadores para uma única tabela.

  • Qualquer outra limitação referente ao tipo de consultas suportadas que se aplica à tarefa de fluxo de dados de origem OLE DB no SSIS também se aplica ao tipo de coletor Consultas T-SQL Genérico.