Creación de la función para recuperar los datos modificados

Artículo
05/30/2023

Se aplica a:SQL Server SSIS Integration Runtime en Azure Data Factory

Después de completar el flujo de control para un paquete de Integration Services que realiza una carga incremental de datos modificados, la tarea siguiente es crear una función con valores de tabla (TVF) que recupere los datos modificados. Solo tiene que crear esta función una vez antes de la primera carga incremental.

Nota

La creación de una función para recuperar los datos modificados es el segundo paso en el proceso de crear un paquete que realiza una carga incremental de datos modificados. Vea una descripción del proceso general para crear este paquete en Captura de datos modificados (SSIS).

Consideraciones de diseño para las funciones de captura de datos modificados (CDC)

Para recuperar los datos modificados, un componente de origen en el flujo de datos del paquete llama a una de las siguientes funciones de consulta de captura de datos modificados:

cdc.fn_cdc_get_net_changes_<capture_instance> Para esta consulta, la única fila devuelta para cada actualización contiene el estado final de cada fila modificada. En la mayoría de los casos, solo necesitará los datos devueltos por una consulta para los cambios netos. Para obtener más información, consulte cdc.fn_cdc_get_net_changes_<capture_instance> (Transact-SQL).
cdc.fn_cdc_get_all_changes_<capture_instance> Esta consulta devuelve todos los cambios que se han producido en cada fila durante el intervalo de captura. Para obtener más información, consulte cdc.fn_cdc_get_all_changes_<capture_instance> (Transact-SQL).

A continuación, el componente de origen toma los resultados devueltos por la función y los pasa a las transformaciones y destinos de nivel inferior, que aplican los datos modificados al destino final.

Pero un componente de origen de Integration Services no puede llamar directamente a estas funciones de captura de datos modificados. Un componente de origen de Integration Services requiere metadatos sobre las columnas devueltas por la consulta. Los funciones de captura de datos modificados no definen las columnas de su tabla de resultados. Así, estas funciones no devuelven metadatos suficientes para un componente de origen de Integration Services .

Use una función de contenedor con valores de tabla, ya que este tipo de función define explícitamente las columnas de la tabla de resultados en su cláusula RETURNS. Esta definición explícita de las columnas proporciona los metadatos que necesita un componente de origen de Integration Services . Debe crear esta función para cada tabla cuyos datos modificados desee recuperar.

Tiene dos opciones para crear la función de contenedor con valores de tabla que llama a la función de consulta de captura de datos modificados:

Puede llamar al procedimiento almacenado del sistema sys.sp_cdc_generate_wrapper_function para crear las funciones con valores de tabla.
Puede escribir su propia función con valores de tabla siguiendo las instrucciones y el ejemplo incluidos en este tema.

Llamada a un procedimiento almacenado para crear la función con valores de tabla

La forma más rápida y sencilla de crear las funciones con valores de tabla que necesita es llamar al procedimiento almacenado del sistema sys.sp_cdc_generate_wrapper_function. Este procedimiento almacenado genera scripts para crear funciones de contenedor diseñadas específicamente para adaptarse a las necesidades de un componente de origen de Integration Services .

Importante

El procedimiento almacenado del sistema sys.sp_cdc_generate_wrapper_function no crea directamente las funciones de contenedor. Lo que hace es generar los scripts CREATE para las funciones de contenedor. El desarrollador debe ejecutar los scripts CREATE generados por el procedimiento almacenado antes de que un paquete de carga incremental llame a las funciones de contenedor.

Para poder entender cómo se usa este procedimiento almacenado del sistema, debe comprender lo que hace, qué scripts genera y qué funciones de contenedor crean los scripts.

Reconocimiento y uso del procedimiento almacenado

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.

Nota

Para más información sobre la sintaxis de este procedimiento almacenado y sus parámetros, consulte sys.sp_cdc_generate_wrapper_function (Transact-SQL).

El procedimiento almacenado siempre genera una función de contenedor para devolver todos los cambios de cada instancia de captura. Si se ha establecido el parámetro @supports_net_changes al crear 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 del nombre de la instancia de captura. El prefijo usado para la función net changes, en caso de que se cree, es «fn_net_changes_».)
La instrucción CREATE para la función de contenedor.

Reconocimiento y uso de los scripts creados por el procedimiento almacenado

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

Reconocimiento y uso de las funciones creadas por el procedimiento almacenado

Para recorrer de forma sistemática la escala de 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.
Si el valor del parámetro @start_time o @end_time supera la hora del LSN más bajo o más alto, la ejecución de las funciones de contenedor generadas devolverá el error 313: Msg 313, Level 16, State 3, Line 1 An insufficient number of arguments were supplied for the procedure or function. El desarrollador debe controlar este error.

La mayoría de los usuarios podrá usar las funciones de contenedor creadas por el procedimiento almacenado del sistema sys.sp_cdc_generate_wrapper_function sin realizar 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, consulte 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.
Marcas de actualización, si las pide, 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.

Escritura de su propia función con valores de tabla

También puede usar SQL Server Management Studio para escribir su propia función de contenedor con valores de tabla que llame a la función de consulta de captura de datos modificados y almacenar la función de contenedor con valores de tabla en SQL Server. Para obtener más información sobre cómo crear una función Transact-SQL, consulte CREATE FUNCTION (Transact-SQL).

En el ejemplo siguiente se define una función con valores de tabla que recupera los cambios de una tabla Customer para el intervalo de cambios especificado. Esta función usa las funciones de captura de datos modificados para asignar los valores de datetime a los valores de número de secuencia de registro (LSN) binario que las tablas de cambios usan internamente. Esta función también controla varias condiciones especiales:

Cuando se pasa un valor nulo para la fecha y hora de inicio, esta función utiliza el primer valor disponible.
Cuando se pasa un valor nulo para la fecha y hora de finalización, esta función utiliza el último valor disponible.
Cuando el LSN inicial es igual que el LSN final, lo que suele implicar que no hay ningún registro para el intervalo seleccionado, esta función finaliza.

Ejemplo de una función con valores de tabla que consulta los datos modificados

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

Recuperación de metadatos adicionales con los datos modificados

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.

Nombre de la columna	Tipo de datos	Descripción
__$start_lsn	binary(10)	Número de secuencia de registro (LSN) asociado con la transacción de confirmación para el cambio. Todos los cambios confirmados en la misma transacción comparten el mismo LSN de confirmación. Por ejemplo, si una operación de actualización en la tabla de origen modifica dos filas diferentes, la tabla de cambios contendrá cuatro filas (dos con los valores anteriores y dos con los valores nuevos), cada una con el mismo valor __$start_lsn .
__$seqval	binary(10)	Valor de secuencia que se usa para ordenar los cambios de fila en una transacción.
__$operation	int	Operación del lenguaje de manipulación de datos (DML) asociada al cambio. Puede ser uno de los siguientes: 1 = eliminar 2 = insertar 3 = actualizar (valores antes de la operación de actualización) 4 = actualizar (valores después de la operación de actualización)
__$update_mask	varbinary(128)	Máscara de bits basada en los ordinales de las columnas de la tabla de cambios que identifica las columnas que han cambiado. Puede examinar este valor para determinar las columnas que han cambiado.
<columnas de la tabla de origen capturadas>	Varía	Las columnas restantes devueltas por la función son las columnas de la tabla de origen que se identificaron como columnas capturadas cuando se creó la instancia de captura. Si no se especificó inicialmente ninguna columna en la lista de columnas capturadas, se devuelven todas las columnas de la tabla de origen.

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

siguiente paso

Una vez que haya creado la función con valores de tabla que consulta los datos modificados, el paso siguiente consiste en comenzar a diseñar el flujo de datos del paquete.

Tema siguiente:Recuperar y describir datos modificados