CDC dans SSIS : chargement incrémental sans balayer toute la table

En près de deux décennies passées à m'occuper d'ETL, peu de douleurs ont été aussi récurrentes que le chargement qui retraite la table entière chaque nuit. Le package s'exécute, relit des millions de lignes à la source, déverse tout dans la destination et, au final, on découvre que seules quelques centaines d'enregistrements ont réellement changé. C'est coûteux pour la base transactionnelle, lent pour la fenêtre de chargement et de plus en plus fragile à mesure que le volume grandit.

La réponse classique dans l'écosystème SQL Server reste valable — et reste sous-estimée : Change Data Capture (CDC) consommé au sein de SSIS, coordonné par le CDC Control Task. Cet article présente le pattern complet, avec du code, et discute honnêtement des cas où il a encore du sens en 2026.

Changer de question

Un chargement complet demande : « à quoi ressemble la table maintenant ? ». Un chargement incrémental demande : « qu'est-ce qui a changé depuis ma dernière exécution ? ». Toute l'économie vient de ce changement de question.

Le CDC répond à la seconde question en lisant le journal des transactions de SQL Server. Lorsque vous activez le CDC sur une table, SQL Server se met à enregistrer, de façon asynchrone, chaque INSERT, UPDATE et DELETE dans une table de changements en miroir (cdc.<schema>_<table>_CT). Votre package SSIS ne touche plus à la table d'origine pour découvrir le delta — il consomme cet enregistrement de changements. Moins d'I/O à la source, moins de lignes en transit, une fenêtre de chargement plus courte.

Étape 1 — Activer le CDC (une seule fois)

Le CDC s'active sur la base, puis sur chaque table que vous voulez suivre. C'est une opération administrative, effectuée une seule fois :

-- 1. active le CDC sur la base
EXEC sys.sp_cdc_enable_db;

-- 2. active le CDC sur la table source
EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'Vendas',
    @role_name     = NULL,          -- pas de rôle de sécurité supplémentaire
    @supports_net_changes = 1;      -- essentiel pour les net changes

Le paramètre @supports_net_changes = 1 exige que la table ait une clé primaire (ou un index unique renseigné) et c'est lui qui permet la lecture des changements nets, expliquée à l'étape 2. À partir de là, deux jobs du SQL Server Agent apparaissent : le capture (lit le journal et alimente les tables de changements) et le cleanup (supprime les changements au-delà de la fenêtre de rétention). Retenez cette information — elle revient dans la section sur les compromis.

Étape 2 — Lire les changements nets (Net changes)

Le CDC offre deux fonctions de requête : all changes et net changes. La différence est décisive pour le data warehousing.

• All changes (fn_cdc_get_all_changes_...) renvoie chaque opération survenue dans la fenêtre. Si une vente a été mise à jour dix fois dans la nuit, vous recevez dix lignes.
• Net changes (fn_cdc_get_net_changes_...) renvoie le résultat net par clé : une seule ligne avec l'état final de cet enregistrement dans la fenêtre. Les dix mises à jour deviennent une seule ligne.

Pour alimenter dimensions et faits, les net changes sont presque toujours ce que vous voulez — moins de lignes, résultat consolidé, prêt pour un MERGE à la destination :

-- convertit les bornes temporelles en LSN
DECLARE @de  BINARY(10) = sys.fn_cdc_map_time_to_lsn(
            'smallest greater than or equal', @data_inicio);
DECLARE @ate BINARY(10) = sys.fn_cdc_map_time_to_lsn(
            'largest less than or equal', @data_fim);

-- lit 1 ligne finale par clé (net)
SELECT *
FROM cdc.fn_cdc_get_net_changes_Vendas(@de, @ate, 'all');

La colonne __$operation indique le type de changement de chaque ligne (1 = delete, 2 = insert, 4 = update), ce qui permet d'orienter chaque enregistrement vers la bonne branche de votre MERGE ou de votre Conditional Split dans le Data Flow.

Étape 3 — Le CDC Control Task et l'état par LSN

Voici la pièce qui transforme un SELECT malin en pipeline fiable. Le grand risque de tout chargement incrémental, c'est l'état : comment savoir, précisément, où le chargement précédent s'est arrêté ? Les dates updated_at sont traîtresses (horloges, fuseaux, transactions longues). Le CDC résout cela avec le LSN (Log Sequence Number) — un marqueur déterministe de la position dans le journal.

Le CDC Control Task de SSIS gère cet état pour vous. Le pattern de package en comporte deux instances, encadrant le Data Flow :

1. CDC Control Task (Get Processing Range) — au début : lit l'état sauvegardé de la dernière exécution et calcule la fenêtre [dernier LSN traité, LSN actuel].
2. Data Flow — au milieu : utilise CDC Source + CDC Splitter pour lire le delta et appliquer inserts/updates/deletes à la destination.
3. CDC Control Task (Mark Processed Range) — à la fin : écrit le nouveau LSN traité dans une table d'état (ex. : dbo.cdc_states).

L'état vit généralement dans une variable du package persistée dans cette table de contrôle. L'effet pratique est ce que tout ingénieur data recherche : l'idempotence. Retraité par erreur ? Relancez — la fenêtre est déterministe et vous ne dupliquez ni ne sautez d'enregistrements. Interrompu en cours de route ? La prochaine exécution reprend exactement là où elle s'est arrêtée.

Le pattern complet du package

En assemblant les pièces, le flux de contrôle d'un package incrémental ressemble à ceci :

[CDC Control Task: Get Processing Range]
                |
                v
        [Data Flow Task]
   CDC Source -> CDC Splitter
        |-> Insert  -> OLE DB Destination
        |-> Update  -> Staging + MERGE
        |-> Delete  -> Staging + DELETE/soft-delete
                |
                v
[CDC Control Task: Mark Processed Range]

Une bonne pratique consiste à ne pas appliquer les UPDATE/DELETE ligne par ligne dans le Data Flow. Dirigez les updates et deletes vers des tables de staging et exécutez un MERGE par lot dans l'Execute SQL Task suivant. C'est plus rapide et transactionnellement plus propre que des milliers de commandes unitaires.

Pourquoi cela compte encore en 2026

Le CDC de SQL Server est une technologie mature, mais le problème qu'elle résout n'a pas vieilli :

• Moins d'I/O à la source. Lire le journal plutôt que balayer la table réduit la pression sur la base transactionnelle — généralement le système le plus sensible du parc.
• Des net changes prêtes pour la destination. Le consolidé par clé s'intègre naturellement dans un MERGE de dimensions et de faits, réduisant la logique de déduplication en aval.
• Un état déterministe par LSN. Des chargements idempotents et réexécutables sont la base de tout pipeline fiable — et le LSN offre cela sans dépendre d'horodatages fragiles.

Pour les nombreuses équipes qui maintiennent encore SSIS on-premises, c'est la manière native, éprouvée et peu coûteuse de mettre le full load à la retraite.

Quand NE PAS l'utiliser (le côté honnête)

Aucune technique n'est une solution miracle. Avant d'activer le CDC partout :

• Rétention du journal et espace disque. Le CDC augmente l'activité du journal des transactions et conserve les tables de changements pendant la fenêtre de rétention configurée. Dimensionnez le cleanup et le stockage.
• Dépendance au SQL Server Agent. Les jobs de capture/cleanup doivent tourner et être supervisés. Si l'Agent s'arrête, le delta prend du retard.
• Scénarios cloud-first. Si vous construisez quelque chose de neuf sur Microsoft Fabric ou sur un lakehouse, évaluez le Copy Job de Fabric Data Factory (qui intègre déjà un CDC géré) ou le CDC natif de la plateforme avant de recréer le pattern on-prem. Le concept est le même ; l'exploitation est plus simple.
• Sources sans journal accessible. Le CDC lit le journal de SQL Server. Pour d'autres sources, le pattern équivalent peut être un CDC basé sur un outil (Debezium, connecteurs) ou un filigrane par colonne.

Conclusion

La question qui sépare un chargement coûteux d'un chargement élégant est simple : « qu'est-ce qui a changé ? » plutôt que « à quoi ressemble tout maintenant ? ». Dans SSIS, le CDC avec le CDC Control Task répond nativement à cette question, avec un état déterministe par LSN et des changements nets prêts pour la destination. C'est un pattern vieux de vingt ans qui continue d'apporter de la valeur — et qui, bien appliqué, raccourcit les fenêtres de chargement, soulage la base source et rend votre pipeline sûr à réexécuter.

Si vous maintenez une grande table en full load, commencez par elle : activez le CDC dans un environnement de test, montez le package avec les deux Control Tasks et comparez la fenêtre de chargement. Les chiffres justifient généralement le changement à eux seuls.