Criar a função para recuperar os dados de alteração

Após concluir o fluxo de controle de um pacote Integration Services que executa uma carga incremental de dados de alteração, a próxima tarefa é criar uma função com valor de tabela que recupera os dados de alteração. É preciso criar esta função apenas uma vez antes da primeira carga incremental.

Observação
A criação de uma função para recuperar os dados de alteração é a segunda etapa no processo de criação de um pacote que realize uma carga incremental de dados de alteração. Para obter uma descrição do processo geral para criar este pacote, consulte Change Data Capture (SSIS).

Considerações de design para funções de captura de dados de alteração

Para recuperar dados de alteração, um componente de origem no fluxo de dados do pacote chama uma das seguintes funções de consulta de captura de dados de alteração:

• cdc.fn_cdc_get_net_changes_<capture_instance>   Para essa consulta, a única linha retornada para cada atualização contém o estado final de cada linha alterada. Na maioria dos casos, você precisa apenas dos dados retornados por uma consulta para alterações líquidas. Para obter mais informações, consulte cdc.fn_cdc_get_net_changes_<capture_instance> (Transact-SQL).

• cdc.fn_cdc_get_all_changes_<capture_instance> esta consulta retorna todas as alterações que ocorreram em cada linha durante o intervalo de captura. Para obter mais informações, consulte cdc.fn_cdc_get_all_changes_<capture_instance> (Transact-SQL).

O componente de origem que recebe os resultados retornados pela função e os passa para transformações e destinos downstream, que aplicam os dados da alteração no destino final.

No entanto, um componente de origem Integration Services não pode chamar estas funções de captura de dados de alteração diretamente. Um componente de origem Integration Services requer metadados sobre as colunas retornadas pela consulta. As funções de captura de dados de alteração não definem as colunas de suas tabelas de saída. Dessa forma, estas funções não retornam metadados suficientes para um componente de origem Integration Services.

Em vez disso, use uma função de invólucro com valor de tabela, pois esse tipo de função define explicitamente as colunas de sua tabela de saída em sua cláusula RETURNS. Esta definição explícita de colunas fornece os metadados que uma componente de origem Integration Services precisa. É necessário criar esta função para cada tabela para a qual você deseja recuperar os dados de alteração.

Você tem duas opções para criar a função de invólucro com valor de tabela que chama a função de consulta de captura de dados de alteração:

• Você chamar o procedimento armazenado de sistema sys.sp_cdc_generate_wrapper_function para criar as funções com valor de tabela.

• Você pode escrever sua própria função com valor de tabela usando as diretrizes e o exemplo neste tópico.

Chamando um procedimento armazenado para criar a função com valor de tabela

A maneira mais rápida e fácil de criar funções com valor de tabela de que você precisa é chamar o procedimento armazenado de sistema sys.sp_cdc_generate_wrapper_function. Esse procedimento armazenado gera scripts para criar funções de invólucro projetadas especificamente para atender às necessidades de um componente de origem do Integration Services.

Importante
O procedimento armazenado de sistema sys.sp_cdc_generate_wrapper_function não cria as funções de invólucro diretamente. Em vez disso, o procedimento armazenado gera os scripts CREATE para as funções de invólucro. O desenvolvedor deve executar os scripts CREATE gerados pelo procedimento armazenado antes de um pacote de carregamento incremental chamar as funções de invólucro.

Para entender como usar esse procedimento armazenado de sistema, você deve entender o que o procedimento faz, quais scripts o procedimento gera e quais funções de invólucro os scripts criam.

Entendendo e usando o procedimento armazenado

O procedimento armazenado de sistema sys.sp_cdc_generate_wrapper_function gera scripts para criar funções de invólucro para uso por pacotes Integration Services.

Estas são as primeiras linhas da definição do procedimento armazenado:

CREATE PROCEDURE sys.sp_cdc_generate_wrapper_function

(

@capture\_instance sysname = null

@closed\_high\_end\_point bit = 1,

@column\_list = null,

@update\_flag\_list = null

)

Todos os parâmetros do procedimento armazenado são opcionais. Se você chamar o procedimento armazenado sem fornecer valores para nenhum dos parâmetros, o procedimento armazenado criará funções de invólucro para todas as instâncias de captura às quais você tem acesso.

Observação
Para obter mais informações sobre a sintaxe desse procedimento armazenado e seus parâmetros, consulte sys.sp_cdc_generate_wrapper_function (Transact-SQL).

O procedimento armazenado sempre gera uma função de invólucro para retornar todas as alterações de cada instância de captura. Se o parâmetro @supports\_net\_changes foi definido quando a instância de captura foi criada, o procedimento armazenado também gerará uma função de invólucro para retornar alterações globais de cada instância de captura de aplicativo.

O procedimento armazenado retorna um conjunto de resultados com duas colunas:

• O nome da função de invólucro que o procedimento armazenado gerou. Esse procedimento armazenado deriva o nome da função do nome do nome da instância de captura. (O nome da função é 'fn_all_changes_' seguido pelo nome da instância de captura. O prefixo usado para a função de alterações globais, se for criado, será 'fn_net_changes_'.)

• A instrução CREATE da função de invólucro.

Entendendo e usando os scripts criados pelo procedimento armazenado

Em geral, um desenvolvedor usa uma instrução INSERT...EXEC para chamar o procedimento armazenado sys.sp_cdc_generate_wrapper_function e salvar os scripts criados por esse procedimento em uma tabela temporária. Cada script pode ser selecionado executado individualmente para criar a função de invólucro correspondente. No entanto, um desenvolvedor também pode usar um conjunto de comandos SQL para executar todos os scripts CREATE, conforme mostrado no código de exemplo a seguir:

create table #wrapper_functions
      (function_name sysname, create_stmt nvarchar(max))
insert into #wrapper_functions
exec sys.sp_cdc_generate_wrapper_function

declare @stmt nvarchar(max)
declare #hfunctions cursor local fast_forward for 
      select create_stmt from #wrapper_functions
open #hfunctions
fetch #hfunctions into @stmt
while (@@fetch_status <> -1)
begin
      exec sp_executesql @stmt
      fetch #hfunctions into @stmt
end
close #hfunctions
deallocate #hfunctions

Entendendo e usando as funções criadas pelo procedimento armazenado

Para percorrer sistematicamente a linha de tempo dos dados de alteração capturados, as funções de invólucro geradas esperam que o parâmetro @end\_time de um intervalo seja o parâmetro @start\_time do intervalo subsequente. Quando essa convenção é seguida, as funções de invólucro geradas podem executar as seguintes tarefas:

• Mapear os valores de data/hora para os valores LSN usados interiormente.

• Assegurar que nenhum dado seja perdido ou repetido.

Para simplificar a consulta a todas as linhas de uma alteração, as funções de invólucro geradas também dão suporte às seguintes convenções:

• Se o parâmetro @start\_time for nulo, as funções de invólucro usarão o valor LSN mais baixo na instância de captura do limite inferior da consulta.

• Se o parâmetro @end\_time for nulo, as funções de invólucro usarão o valor LSN mais alto na instância de captura do limite superior da consulta.

A maioria dos usuários deverá ser capaz de usar as funções de invólucro que o procedimento armazenado de sistema sys.sp_cdc_generate_wrapper_function cria sem modificação. No entanto, para personalizar as funções de invólucro, você tem que personalizar os scripts CREATE antes de executar os scripts.

Quando seu pacote chama as funções de invólucro, o pacote deve fornecer valores para três parâmetros. Esses três parâmetros são como os três parâmetros que as funções de captura de dados de alteração usam. Esses três parâmetros são os seguintes:

• O valor de data/hora inicial e o valor de data/hora final do intervalo. Enquanto as funções de invólucro usam valores de data/hora como os pontos finais do intervalo de consulta, as funções de captura de dados de alteração usam dois valores LSN como os pontos finais.

• O filtro de linha. Para as funções de invólucro e as funções de captura de dados de alteração, o parâmetro @row\_filter\_option é o mesmo. Para obter mais informações, consulte cdc.fn_cdc_get_all_changes_<capture_instance> (Transact-SQL) e cdc.fn_cdc_get_net_changes_<capture_instance> (Transact-SQL).

O conjunto de resultados retornado pelas funções de invólucro inclui os seguintes dados:

• Todas as colunas solicitadas de dados de alteração.

• Uma coluna denominada __CDC_OPERATION que usa um campo de um ou dois caracteres para identificar a operação associada à linha. Os valores válidos para esse campo são os seguintes: ‘I’ de inserir, ‘D’ de excluir, ‘UO’ de atualizar valores antigos e ‘UN’ de atualizar valores novos.

• Os sinalizadores de atualização, quando você os solicita, aparecem como colunas de bit após o código da operação e na ordem especificada no parâmetro @update\_flag\_list. Essas colunas são denominadas com a anexação de '_uflag' ao nome de coluna associado.

Se o pacote chamar uma função de invólucro que consulte todas as alterações, essa função também retornará as colunas __CDC_STARTLSN e __CDC_SEQVAL. Essas duas colunas se tornam a primeira e a segunda colunas, respectivamente, do conjunto de resultados. A função de invólucro também classifica o conjunto de resultados com base nessas duas colunas.

Escrevendo sua própria função do valor de tabela

Também é possível usar SQL Server Management Studio para escrever sua própria função de invólucro com valor de tabela que chama a função de consulta de captura dos dados de alteração e armazena a função de invólucro com valor de tabela no SQL Server. Para obter mais informações sobre como criar uma função Transact-SQL, consulte CREATE FUNCTION (Transact-SQL).

O seguinte exemplo define uma função com valor de tabela que recupera alterações de uma tabela Cliente para o intervalo de alteração especificado. Essa função usa funções de captura de dados de alteração para mapear os valores datetime para os valores LSN (número de sequência de log) binários usados pelas tabelas de alteração internamente. Esta função também controla diversas condições especiais:

• Quando um valor nulo é passado como a hora inicial, essa função usa o valor mais baixo disponível.

• Quando um valor nulo é passado como a hora final, essa função usa o valor mais alto disponível.

• Quando o LSN inicial é igual ao LSN final, que normalmente indica que não existe registros para o intervalo selecionado, essa função é encerrada.

Exemplo de uma função com valor de tabela que consulta a existência de dados de alteração

CREATE function CDCSample.uf_Customer (
     @start_time datetime
    ,@end_time datetime
)
returns @Customer table (
     CustomerID int
    ,TerritoryID int
    ,CustomerType nchar(1)
    ,rowguid uniqueidentifier
    ,ModifiedDate datetime
    ,CDC_OPERATION varchar(1)
) as
begin
    declare @from_lsn binary(10), @to_lsn binary(10)

    if (@start_time is null)
        select @from_lsn = sys.fn_cdc_get_min_lsn('Customer')
    else
        select @from_lsn = sys.fn_cdc_increment_lsn(sys.fn_cdc_map_time_to_lsn('largest less than or equal',@start_time))

    if (@end_time is null)
        select @to_lsn = sys.fn_cdc_get_max_lsn()
    else
        select @to_lsn = sys.fn_cdc_map_time_to_lsn('largest less than or equal',@end_time)

    if (@from_lsn = sys.fn_cdc_increment_lsn(@to_lsn))
        return

    -- Query for change data
    insert into @Customer
    select 
        CustomerID,    
        TerritoryID, 
        CustomerType, 
        rowguid, 
        ModifiedDate, 
        case __$operation
                when 1 then 'D'
                when 2 then 'I'
                when 4 then 'U'
                else null
         end as CDC_OPERATION
    from 
        cdc.fn_cdc_get_net_changes_Customer(@from_lsn, @to_lsn, 'all')

    return
end 
go

Recuperando metadados adicionais com os dados de alteração

Embora a função com valor de tabela criada pelo usuário mostrada anteriormente use apenas a coluna __$operation, a função cdc.fn_cdc_get_net_changes_<capture_instance> retorna quatro colunas de metadados para cada linha de alteração. Se quiser usar esses valores no seu fluxo de dados, poderá retorná-los como colunas adicionais com a função de invólucro com valor de tabela.

Nome da coluna	Tipo de dados	Descrição
__$start_lsn	binary(10)	LSN associado à transação de confirmação da alteração. Todas as alterações confirmadas na mesma transação compartilham o mesmo LSN de confirmação. Por exemplo, se uma operação de atualização na tabela de origem modificar duas linhas diferentes, a tabela de alteração conterá quatro linhas (duas com os valores antigos e duas com os valores novos), cada um com o mesmo valor de __$start_lsn.
__$seqval	binary(10)	Valor de sequência usado para organizar as alterações de linha em uma transação.
__$operation	int	A operação DML (linguagem de manipulação de dados) associada à alteração. Pode ser uma destas opções: 1 = excluir 2 = inserir 3 = atualizar (valores anteriores à operação de atualização). 4 = atualizar (valores posteriores à operação de atualização).
__$update_mask	varbinary(128)	Uma máscara de bits com base nos ordinais de coluna da tabela de alteração identificando as colunas que foram alteradas. Você poderia examinar este valor se você tivesse que determinar quais colunas foram alteradas.
<colunas da tabela de origem capturadas>	varia	As colunas restantes retornadas pela função são as colunas da tabela de origem que foram identificadas como colunas capturadas quando a instância de captura foi criada. Se nenhuma coluna tiver sido especificada originalmente na lista de colunas capturadas, todas as colunas da tabela de origem serão retornadas.

Para obter mais informações, consulte cdc.fn_cdc_get_net_changes_<capture_instance> (Transact-SQL).

Próxima Etapa

Após ter criado a função com valor de tabela que consulta a existência de dados de alteração, a próxima etapa será iniciar a criação do fluxo de dados no pacote.

Próximo tópico: Recuperar e compreender os dados de alteração

Fique atualizado com o Integration Services Para obter os downloads, artigos, exemplos e vídeos mais recentes da Microsoft, bem como soluções selecionadas pela comunidade, visite a página do Integration Services no MSDN: Visite a página do Integration Services no MSDN Para receber uma notificação automática dessas atualizações, assine os RSS feeds disponíveis na página.