Crear la función para recuperar los datos modificados

El procedimiento almacenado del sistema sys.sp_cdc_generate_wrapper_function genera los scripts necesarios para crear las funciones de contenedor que usarán los paquetes de Integration Services.

Estas son las primeras líneas de la definición del procedimiento almacenado:

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 los parámetros del procedimiento almacenado son opcionales. Si llama al procedimiento almacenado sin haber proporcionado valores para alguno de los parámetros, dicho procedimiento creará funciones de contenedor para todas las instancias de captura a las que usted tenga acceso.

El procedimiento almacenado siempre genera una función de contenedor para devolver todos los cambios de cada instancia de captura. Si el parámetro @supports\_net\_changes se estableció cuando se creó la instancia de captura, el procedimiento almacenado también generará una función de contenedor para devolver los cambios netos de cada instancia de captura aplicable.

El procedimiento almacenado devuelve un conjunto de resultados con dos columnas:

• El nombre de la función de contenedor generada por el procedimiento almacenado. Este procedimiento almacenado obtiene el nombre de la función a partir del nombre de la instancia de captura. (El nombre de la función es 'fn_all_changes_' seguido por el nombre de la instancia de captura. El prefijo usado para la función de cambios netos, si se crea, es 'fn_net_changes_'.)

• La instrucción CREATE para la función de contenedor.

Normalmente, un desarrollador usa una instrucción INSERT...EXEC para llamar al procedimiento almacenado sys.sp_cdc_generate_wrapper_function y guarda los scripts creados por dicho procedimiento en una tabla temporal. Una vez hecho esto, se podrá seleccionar y ejecutar cada script de manera individual para crear la función de contenedor correspondiente. Sin embargo, un desarrollador también puede usar un conjunto de comandos SQL para ejecutar todos los scripts CREATE, tal y como se muestra en el código de ejemplo siguiente:

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

Para recorrer sistemáticamente la línea del tiempo de los datos modificados capturados, las funciones de contenedor generadas esperan que el parámetro @end\_time de un intervalo sea el parámetro @start\_time del intervalo siguiente. Cuando se sigue esta convención, las funciones de contenedor generadas pueden realizar las tareas siguientes:

• Asignar los valores de fecha y hora a los valores LSN que se usan internamente.

• Asegurarse de que no se pierde ni se repite ningún dato.

Para simplificar las consultas de todas las filas de una tabla de cambios, las funciones de contenedor generadas también admiten las convenciones siguientes:

• Si el parámetro @start\_time es NULL, las funciones de contenedor usan el valor LSN más bajo de la instancia de captura como el límite inferior de la consulta.

• Si el parámetro @end\_time es NULL, las funciones de contenedor usan el valor LSN más alto de la instancia de captura como el límite superior de la consulta.

La mayoría de los usuarios deberían poder usar las funciones de contenedor creadas por el procedimiento almacenado del sistema sys.sp_cdc_generate_wrapper_function sin llevar a cabo ninguna modificación. Sin embargo, para personalizar las funciones de contenedor, es necesario personalizar los scripts CREATE antes de ejecutarlos.

Cuando el paquete llame a las funciones de contenedor, el paquete debe proporcionar valores para tres parámetros. Estos tres parámetros son como los tres parámetros que usan las funciones de captura de datos modificados. Los tres parámetros son los siguientes:

• Valor de fecha y hora de inicio, y valor de fecha y hora de finalización del intervalo. Aunque las funciones de contenedor usan valores de fecha y hora como los puntos inicial y final del intervalo de consulta, las funciones de captura de datos modificados usan dos valores LSN como dichos puntos.

• El filtro de filas. Para las funciones de contenedor y las funciones de captura de datos modificados, el parámetro @row\_filter\_option es el mismo. Para obtener más información, vea cdc.fn_cdc_get_all_changes_<capture_instance> (Transact-SQL) y cdc.fn_cdc_get_net_changes_<capture_instance> (Transact-SQL).

El conjunto de resultados devuelto por las funciones de contenedor incluye los datos siguientes:

• Todas las columnas de datos modificados solicitadas.

• Una columna denominada __CDC_OPERATION que usa un campo de uno o dos caracteres para identificar la operación que está asociada a la fila. Los valores válidos para este campo son los siguientes: ‘I’ para insertar, ‘D’ para eliminar, ‘UO’ para actualizar valores antiguos y ‘UN’ para actualizar valores nuevos.

• Marcadores de actualización, si se solicitan, que aparecen como columnas de bits después del código de operación y en el orden especificado en el parámetro @update\_flag\_list. El nombre de estas columnas se obtiene anexando ‘_uflag’ al nombre de columna asociado.

Si el paquete llama a una función de contenedor que consulta todos los cambios, dicha función también devolverá las columnas __CDC_STARTLSN y __CDC_SEQVAL. Estas dos columnas se convierten en la primera y en la segunda columna, respectivamente, del conjunto de resultados. La función de contenedor también ordena el conjunto de resultados basándose en estas dos columnas.

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

Aunque la función con valores de tabla creada por el usuario mostrada anteriormente solo usa la columna __$operation, la función cdc.fn_cdc_get_net_changes_<capture_instance> devuelve cuatro columnas de metadatos por cada fila de datos modificados. Si desea utilizar estos valores en el flujo de datos, puede devolverlos como columnas adicionales desde la función contenedora con valores de tabla.

Para obtener más información, vea cdc.fn_cdc_get_net_changes_<capture_instance> (Transact-SQL).