[SSIS] Change Data Capture (CDC) dans SSIS 2012

Le change data capture est un concept qui n'est pas propre à SQL Server (présent dans d'autres SGBD tels que Oracle), et qui consiste à suivre et à récupérer les changements sur les données dans une table.
Il a été implémenté au niveau de SQL Server 2008, mais exclusivement au niveau du moteur de base de données : beaucoup d'articles de blog en ont parlé à l'époque, je vous invite à vous référer à l'article de François Jehl.

Un des côtés fastidieux du CDC était le traçage par LSN et donc l'écriture des requêtes qui pouvait être pénible sous SSIS.

Dans SQL Server 2012, Microsoft va plus loin dans sa démarche et nous donne 3 composants pour utiliser le CDC directement dans SSIS :

• Une tâche CDC Control Task qui va gérer le cycle de vie des packages CDC, et notamment toute la mécanique des LSN.
• Deux composants pour DataFlow Task, un CDC Source qui, comme son nom l'indique lit les informations d'une table de change data capture, et un CDC Splitter qui, lui va rediriger les lignes selon qu'elles doivent être insérées, mises à jour ou supprimées.

En pratique :

On va créer un package SSIS utilisant les composant CDC pour alimenter une table finale à partir d'une table source.

Je crée une table source de tarifs de produit dans laquelle j'insère un jeu de données test.

Sur cette table j'active le CDC. Ici c'est la même chose que sous SQL Server 2008, on retrouve les mêmes commandes.

Package d'initialisation 

Dans SSIS, je crée mon package d'initialisation qui va effectuer la première alimentation de ma table destination et initialiser une variable d'état du CDC.

Le package se présente sous la forme suivante :

Les paramètres à saisir dans le CDC Control Task Start sont les suivants :

• Dans "SQL Server CDC database ADO.NET connection manager", renseigner le connection manager SSIS où est utilisé le change data capture
• Dans "CDC control operation", sélectionner "Mark initial load start"
• Dans "Variable containing the CDC state", donner le nom d'une variable où l'on va stocker l'état du CDC. Cliquer sur New s'il s'agit du premier appel.
• Les 3 champs suivants definissent respectivement la base de données, la table et le champ de la table où l'on stocke l'état du CDC

Le flux de contrôle "CDC Control Task End" est à configurer de la même manière, sauf que l'on sélectionne "Mark initial load end" dans "CDC control operation"

Le dataflow Task consiste en un bête chargement de table :

On peut dès lors exécuter le package et vérifier les données que j'ai dans ma table destination.

Package d'alimentation quotidien

On crée maintenant le package d'alimentation quotidien : il ressemble à notre package initial dans sa structure (toujours un CDC control task suivi d'un dataflow task et d'un autre CDC control task).

Dans le CDC Control Task Start on sélectionne "Get processing range" dans le "CDC control operation" :

Dans le CDC Control Task End, on sélectionne "Mark processing range" dans le "CDC control operation" :

Le dataflow task est maintenant développé de la manière suivante, à l'aide des deux nouveaux composants SSIS (CDC source et CDC splitter) :

Je m'attarde sur le CDC source, notamment au niveau du CDC processing mode. Je vous laisse consulter l'article suivant de Matt Masson (qui fait partie de l'équipe de développement de SSIS) pour comprendre pourquoi j'ai choisi "Net" :

J'effectue quelques requêtes de modifications de données dans ma table source et j'exécute ensuite mon package.

Dans mon dataflow, on retrouve bien les modifications effectuées sur ma table source, et ma table de destination est bien à la fin de l'exécution semblable à la table source :

Là où ça devient intéressant est que si vous relancez le package, aucune ligne n'est générée dans le dataflow. SSIS a géré tout seul les LSN grâce à nos CDC control task, et sait qu'il n'y a pas eu de changement pas rapport à la précédente exécution.

Conclusion :

SSIS 2012 implémente la gestion en natif du change data capture et simplifie son utilisation. La gestion pénible des LSN pour dater les changements devient implicite : aucune requête à écrire dans mon dataflow task au niveau des sources et aucune manipulation de dates à implémenter :
En revanche, il faut garder à l'esprit que le CDC peut être gourmand en ressources sur vos bases sources, comme c'était déjà le cas sur SQL Server 2008.