Capture de données modifiées (SSIS)

S’applique à :SQL Server SSIS Integration Runtime dans Azure Data Factory

Dans SQL Server, la capture de données modifiées constitue une solution efficace au défi de l'optimisation des chargements incrémentiels de tables sources vers des mini-Data Warehouses et des entrepôts de données.

Qu'est-ce que la capture de données modifiées ?

Les tables sources changent au fil du temps. Un mini-Data Warehouse ou un entrepôt de données qui est basé sur ces tables doit refléter ces modifications. Toutefois, un processus qui copie périodiquement un instantané de la source entière consomme trop de temps et de ressources. D'autres approches, notamment des colonnes timestamp, des déclencheurs ou des requêtes complexes, nuisent souvent aux performances et accroissent la complexité. Un flux de données modifiées fiable est donc nécessaire, structuré de sorte que les consommateurs puissent facilement l'appliquer aux représentations cibles des données. La capture de données modifiées dans SQL Server offre cette solution.

La fonction de capture des modifications de données de Moteur de base de données capture les activités d’insertion, de mise à jour et de suppression appliquées aux tables SQL Server , et rend les détails des modifications disponibles dans un format relationnel simple à utiliser. Les tables de modifications utilisées par la capture de données modifiées contiennent des colonnes qui reflètent la structure de colonne des tables sources suivies, ainsi que les métadonnées nécessaires à la compréhension des modifications effectuées ligne par ligne.

Fonctionnement de la capture de données modifiées dans Integration Services

Un package Integration Services peut facilement collecter les données modifiées dans les bases de données SQL Server pour effectuer des chargements incrémentiels efficaces dans un entrepôt de données. Toutefois, avant de pouvoir utiliser Integration Services pour charger des données modifiées, un administrateur doit activer la capture de données modifiées sur la base de données et les tables à partir desquelles vous souhaitez capturer les modifications. Pour plus d’informations sur la configuration de la capture des changements de données sur une base de données, consultez Activer et désactiver la capture des changements de données (SQL Server).

Une fois qu'un administrateur a activé la capture de données modifiées sur la base de données, vous pouvez créer un package qui effectue un chargement incrémentiel des données modifiées. Le diagramme suivant montre les étapes à suivre pour créer un tel package qui effectue un chargement incrémentiel à partir d'une table individuelle :

Comme indiqué dans le diagramme précédent, la création d'un package qui effectue un chargement incrémentiel des données modifiées implique les étapes suivantes :

Étape 1 : Conception du flux de contrôle
Dans le flux de contrôle du package, les tâches suivantes doivent être définies :

• Calculer les valeurs datetime de début et de fin pour l’intervalle des modifications apportées aux données sources à récupérer.

  Pour calculer ces valeurs, utilisez une tâche d’exécution de requêtes SQL ou des expressions Integration Services avec des fonctions datetime . Vous stockez ensuite ces points de terminaison dans des variables de package pour une utilisation ultérieure dans le package.

  Pour plus d’informations, consultezSpécifier un intervalle de données modifiées

• Déterminer si les données modifiées pour l'intervalle sélectionné sont prêtes. Cette étape est nécessaire car le processus de capture asynchrone n'a peut-être pas encore atteint le point de terminaison sélectionné.

  Pour déterminer si les données sont prêtes, commencez si nécessaire par un conteneur de boucles For pour différer l'exécution, jusqu'à ce que les données modifiées pour l'intervalle sélectionné soient prêtes. Dans le conteneur de boucles, utilisez une tâche d'exécution SQL pour interroger les tables de mappage du temps gérées par la capture de données modifiées. Utilisez ensuite une tâche de script qui appelle la méthode Thread.Sleep ou une autre tâche d’exécution SQL avec une instruction WAITFOR pour différer temporairement l’exécution du package si nécessaire. Utilisez éventuellement une autre tâche de script pour enregistrer une condition d'erreur ou un délai d'attente.

  Pour plus d’informations, consultezDéterminer si les données modifiées sont prêtes

• Préparer la chaîne de requête qui sera utilisée pour rechercher les données modifiées.

  Utilisez une tâche de script ou une tâche d'exécution SQL pour assembler l'instruction SQL qui sera utilisée pour rechercher les modifications.

  Pour plus d’informations, consultezPréparer la recherche des données modifiées

Étape 2 : Configuration de la requête pour rechercher les données modifiées
Créez la fonction table qui recherchera les données.

Utilisez SQL Server Management Studio pour développer et enregistrer la requête.

Pour plus d’informations, consultezRécupérer et comprendre les données modifiées

Étape 3 : Conception du flux de données
Dans le flux de données du package, les tâches suivantes doivent être définies :

• Récupérer les données modifiées des tables de modifications.

  Pour récupérer les données, utilisez un composant source pour interroger les tables de modifications à propos des modifications qui se situent dans l'intervalle sélectionné. La source appelle une fonction table Transact-SQL que vous aurez créée précédemment.

  Pour plus d’informations, consultezRécupérer et comprendre les données modifiées

• Fractionner les modifications en insertions, mises à jour et suppressions à des fins de traitement.

  Pour fractionner les modifications, utilisez une transformation de fractionnement conditionnel pour diriger les insertions, les mises à jour et les suppressions vers les différentes sorties pour un traitement approprié.

  Pour plus d’informations, consultezTraiter les insertions, les mises à jour et les suppressions

• Appliquer les insertions, les suppressions et les mises à jour à la destination.

  Pour appliquer les modifications à la destination, utilisez un composant de destination pour appliquer les insertions à la destination. Ensuite, utilisez des transformations de commande OLE DB avec des instructions UPDATE et DELETE paramétrables pour appliquer les mises à jour et les suppressions à la destination. Vous pouvez également appliquer les mises à jour et les suppressions en utilisant des composants de destination pour enregistrer les lignes dan des tables temporaires. Ensuite, utilisez des tâches d'exécution SQL pour effectuer les opérations de mise à jour en bloc et de suppression en bloc sur la destination à partir des tables temporaires.

  Pour plus d’informations, consultezAppliquer des modifications à la destination

Données modifiées en provenance de plusieurs tables

Le processus exposé précédemment fait référence à un chargement incrémentiel à partir d'une table unique. Pour effectuer un chargement incrémentiel à partir de plusieurs tables, le processus d'ensemble est le même. Toutefois, la conception du package doit être modifié pour prendre en charge le traitement de plusieurs tables. Pour plus d’informations sur la création d’un package qui effectue un chargement incrémentiel à partir de plusieurs tables, consultez Exécuter un chargement incrémentiel de plusieurs table.

Tâches associées

• Spécifier un intervalle de données modifiées

• Déterminer si les données modifiées sont prêtes

• Préparer la recherche des données modifiées

• Créer la fonction de récupération des données modifiées

• Récupérer et comprendre les données modifiées

• Traiter les insertions, les mises à jour et les suppressions

• Appliquer des modifications à la destination

• Exécuter un chargement incrémentiel de plusieurs table

Contenu associé

Entrée de blog, SSIS Design Pattern - Incremental Load, sur sqlblog.com