Captura de datos modificados (SSIS)

  • Artículo

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

En SQL Server, la captura de datos modificados ofrece una solución efectiva al desafío de realizar eficazmente las cargas incrementales de las tablas de origen a los data mart y a los almacenamientos de datos.

¿Qué es la captura de datos modificados?

Las tablas de origen cambian con el tiempo. Un data mart o un almacenamiento de datos basado en dichas tablas necesita reflejar estos cambios. Sin embargo, un proceso que copie periódicamente una instantánea del origen completo consume demasiado tiempo y recursos. Los métodos alternativos que hacen uso de columnas de marca de tiempo, desencadenadores o consultas complejas suelen afectar al rendimiento y aumentar la complejidad. Lo que se necesita es un flujo de datos modificados confiable y estructurado de forma que los consumidores puedan aplicarlo fácilmente a las representaciones de destino de los datos. La captura de datos modificados de SQL Server proporciona esta solución.

El mecanismo de captura de datos modificados de Motor de base de datos captura las operaciones de inserción, actualización y eliminación aplicadas a las tablas de SQL Server y proporciona los detalles de los cambios en un formato relacional fácil de utilizar. Las tablas de cambios utilizadas por la captura de datos modificados contienen las columnas que reflejan la estructura de columnas de las tablas de origen a las que se realiza el seguimiento junto con los metadatos necesarios para entender los cambios que se han producido en cada fila.

Nota:

La captura de datos modificados no está disponible en todas las ediciones de Microsoft SQL Server. Para obtener una lista de las características admitidas por las ediciones de SQL Server, vea Características compatibles con las ediciones de SQL Server 2016.

Cómo funciona la captura de datos modificados en Integration Services

Un paquete de Integration Services puede recopilar con facilidad los datos modificados en las bases de datos de SQL Server para realizar cargas incrementales con eficacia en un almacenamiento de datos. Sin embargo, antes de poder utilizar Integration Services para cargar los datos modificados, el administrador debe habilitar la captura de datos modificados en la base de datos y en las tablas cuyos cambios se desean capturar. Para obtener más información sobre cómo configurar la captura de datos modificados en una base de datos, vea Habilitar y deshabilitar la captura de datos modificados (SQL Server).

Cuando el administrador ha habilitado la captura de datos modificados en la base de datos, es posible crear un paquete que realiza una carga incremental de los datos modificados. El diagrama siguiente muestra los pasos necesarios para crear un paquete de este tipo que realiza una carga incremental desde una sola tabla:

Pasos de creación de un paquete de captura de datos modificados

Tal como se muestra en el diagrama anterior, la creación de un paquete que realiza una carga incremental de datos modificados conlleva los pasos siguientes:

Paso 1: diseñar el flujo de control
Debe definir las tareas siguientes en el flujo de control del paquete:

  • Calcule los valores datetime inicial y final para el intervalo de cambios en los datos de origen que desea recuperar.

    Para calcular estos valores, use una tarea Ejecutar SQL o expresiones de Integration Services con funciones datetime . A continuación, almacene estos extremos en variables de paquete para usarlas posteriormente en el paquete.

    Para obtener más información:Especificar un intervalo de datos modificados

  • Determine si están listos los datos modificados para el intervalo seleccionado. Este paso es necesario porque es posible que el proceso de captura asincrónico todavía no haya alcanzado el extremo seleccionado.

    Para determinar si están listos los datos, comience con un contenedor de bucles For para retrasar la ejecución, si es necesario, hasta que estén listos los datos modificados para el intervalo seleccionado. Dentro del contenedor de bucles, utilice una tarea Ejecutar SQL para consultar las tablas de asignación de fecha y hora que se mantienen en la captura de datos modificados. A continuación, utilice una tarea Script que llame al método Thread.Sleep u otra tarea Ejecutar SQL con una instrucción WAITFOR , con objeto de retrasar la ejecución del paquete temporalmente, si es necesario. También puede utilizar otra tarea Script para registrar una condición de error o un tiempo de espera.

    Para obtener más información:Determinar si los datos modificados están preparados

  • Prepare la cadena de consulta que se utilizará para consultar los datos modificados.

    Use una tarea Script o Ejecutar SQL para ensamblar la instrucción SQL que se usará para consultar si hay cambios.

    Para obtener más información:Preparar para consultar datos modificados

Paso 2: configurar la consulta para los datos modificados
Cree la función con valores de tabla que consultará los datos.

Utilice SQL Server Management Studio para desarrollar y guardar la consulta.

Para obtener más información:Recuperar y describir datos modificados

Paso 3: diseñar el flujo de datos
Debe definir las tareas siguientes en el flujo de datos del paquete:

  • Recupere los datos modificados de las tablas de cambios.

    Para recuperar los datos, utilice un componente de origen para consultar las tablas de cambios con objeto de detectar los cambios comprendidos en el intervalo seleccionado. El origen llama a una función con valores de tabla de Transact-SQL que deberá haber creado previamente.

    Para obtener más información:Recuperar y describir datos modificados

  • Divida los cambios en inserciones, actualizaciones y eliminaciones para su procesamiento.

    Para dividir los cambios, utilice una transformación División condicional para dirigir las inserciones, las actualizaciones y las eliminaciones a las distintas salidas con objeto de procesarlas adecuadamente.

    Para obtener más información:Procesar inserciones, actualizaciones y eliminaciones

  • Aplique las inserciones, eliminaciones y actualizaciones en el destino.

    Con el fin de realizar los cambios en el destino, utilice un componente de destino para aplicar las inserciones en el destino. Asimismo, utilice transformaciones Comando de OLE DB con instrucciones UPDATE y DELETE con parámetros para aplicar las actualizaciones y las eliminaciones en el destino. También puede aplicar las actualizaciones y las eliminaciones mediante componentes de destino para guardar las filas en tablas temporales. A continuación, utilice tareas Ejecutar SQL para realizar operaciones de actualización y eliminación masivas en el destino a partir de las tablas temporales.

    Para obtener más información:Aplicar los cambios al destino

Datos modificados de varias tablas

El proceso descrito en el diagrama y en los pasos anteriores conlleva una carga incremental a partir de una sola tabla. Cuando es preciso realizar una carga incremental a partir de varias tablas, el proceso general es el mismo. Sin embargo, hay que cambiar el diseño del paquete para adaptarlo al procesamiento de varias tablas. Para obtener más información sobre cómo crear un paquete que realiza una carga incremental a partir de varias tablas, vea Realizar una carga incremental de varias tablas.

Contenido relacionado

Entrada de blog sobre el modelo de diseño de SSIS y la carga incremental en sqlblog.com.