Crear la función para recuperar los datos modificados

Artículo
05/08/2013

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 consiste en crear una función con valores de tabla que recupere dichos datos. 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. Para obtener una descripción del proceso general de creación de este paquete, vea Captura de datos modificados (SSIS).

Consideraciones de diseño para las funciones de captura de datos modificados

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 fila única 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, vea 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, vea 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.

Sin embargo, un componente de origen de Integration Services no puede llamar a estas funciones de captura de datos modificados directamente. 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, que crea funciones con valores de tabla automáticamente.
Puede escribir su propia función con valores de tabla siguiendo las instrucciones y el ejemplo incluidos en este tema.

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

La manera más rápida y más fácil 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 satisfacer las necesidades de un componente de origen de Integration Services.

Importante
El procedimiento almacenado del sistema sys.sp_cdc_generate_wrapper_function no crea las funciones de contenedor directamente. 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.

Entender y usar el 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 obtener más información sobre la sintaxis de este procedimiento almacenado y sus parámetros, vea 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 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.

Entender y usar 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 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

Entender y usar las funciones creadas por el procedimiento almacenado

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

Escribir 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 dicha función en SQL Server. Para obtener más información acerca de cómo crear una función de Transact-SQL, vea 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 utiliza las funciones de captura de datos modificados para asignar los valores datetime a los valores de número de flujo de registro binario (LSN) que las tablas de cambios utilizan 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

Recuperar 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 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 una de las 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, vea cdc.fn_cdc_get_net_changes_<capture_instance> (Transact-SQL).

Paso siguiente

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

Manténgase al día con Integration Services

Para obtener las descargas, los artículos, los ejemplos y los vídeos más recientes de Microsoft, así como una selección de soluciones de la comunidad, visite la página de Integration Services en MSDN:

Visite la página de Integration Services en MSDN

Para recibir notificaciones automáticas de estas actualizaciones, suscríbase a las fuentes RSS disponibles en la página.

Compartir a través de