Développement configuration et déploiement avec SSIS 2012

SSIS 2012 offre une toute nouvelle manière de gérer la configuration des packages. Cette nouvelle façon de gérer la configuration est accompagnée d’un nouveau modèle de déploiement.

Cet article aura pour but de décortiquer ces nouveaux usages. Est ce qu’il y a un impact sur le développement ? sur la gestion des packages sur le server ? etc.

Côté développement

Petite piqûre de rappel, avec SSIS 2012 il n’ y a plus de fichier de configuration.

Dans SQL Server Data Tools on a maintenant des paramètres. Ces paramètres peuvent avoir deux portées (scope) soit une portée Package soit une portée Projet. Il faut voir la portée Projet comme des « variables globales » de notre projet SSIS et la portée package comme des « variables globales du package ». Par exemple : nom de server, chemin d’accès d’un emplacement sur le réseau etc.

Cela ressemble étrangement aux variables qu’on stockait dans notre fichier de configuration en 2008…
Attention, la notion de paramètre peut être mêlée avec la notion de variables, sauf que ce sont deux notions totalement différentes.

Déjà en terme d’utilisation et sans compter que les types supportés par les paramètres sont différents des types des variables. Par exemple il n’y a pas de type Objet dans les paramètres.
Les paramètres peuvent être utilisées dans les expressions et leur nomenclature diffère de celle des variables : @[$Project::ParameterName]

On peut voir les paramètres à deux endroits :

Si le scope est Projet : Solution Explorer, en double cliquant sur Project.params

Si le scope est package  : Onglet Parameters du package.

Ces paramètres nous aident donc à faire la configuration de nos packages SSIS. Donc oui il y a un impact sur le développement comme c’était déjà le cas en 2008. Il faut donc utiliser impérativement ces paramètres pour rendre le développement configurable et exécutable dans différents environnements.

Par contre tout les éléments d’une solution SSIS ne peuvent être paramétrés. On peut paramétrer la plupart des tâches du control flow et les connexions du Connection Manager.

En parlant des connexions, elles peuvent désormais être converties en connexions de projet, ce qui permet de les rendre disponibles à l’ensemble des packages du projet SSIS.

ProtectionLevel

Le niveau de proctection des packages SSIS, joue également un rôle dans cette floppée de configuration, la propriété ProtectionLevel existe toujours au niveau du package et elle est présente également dans les propriétés du projet.

Un dernier point intéressant, on peut créer différentes configurations dans notre projet SSIS, grâce au configuration Manager de Visual Studio,ce n’est pas nouveau on pouvait le faire en 2008. Personnellement je ne jamais touché à cela…

Mais grâce à ceci on peut changer la valeur des paramètres durant le Design Time. Donc être capable de déveloper et debugger selon différentes configurations.

Le déploiement

Une fois le développement terminé et les paramêtres en place on peut passer au déploiement.

Pour un simple développeur, un clic droit sur la solution puis deploy.

Mais il existe d’autres méthodes de déploiement. Sachez tout de même que lorsqu’on Build un projet SSIS, un fichier .ispac est créé dans le dossier Bin de votre projet.

Attention : si vous utilisez des configurations choisissez la bonne avant de compiler.

Si vous cliquez sur ce .ispac l’assistant de déploiement va se lancer.

Que contient ce fichier .ispac ?
Si vous changez l’extension du fichier en .zip ou .cab vous allez pouvoir voir le contenu. Vous aller retrouver tous les éléments de votre projet SSIS (package, paramètre, fichier xml de configuration)

Côté administration

Avec SQL Server 2012 on a un server dédié SSIS : Integration Services Catalog. Ce qui sous entend qu’on fait un déploiement en MSDB.

A partir de ce catalogue on peut configurer nos packages en attribuant des valeurs aux paramètres définis dans le développement. On peut avoir plusieurs jeux de valeurs de paramètres car on est capable désormais de définir des environements.

Ces environements pemettent donc d’exécuter des packages dans un certain contexte.

Pour chaque environnement on va définir un ensemble de variables.

Conseil : je ne connais pas encore quelles sont les bonnes pratiques à avoir avec cette nouvelle façon de procéder, mais je vous conseille de garder les mêmes noms de variables dans chaque environnement : donc de reproduire le même set de variables.

Et on va pouvoir configurer notre projet SSIS. Un clic droit Configure sur le projet.

Le premier panneau de configuration vous permet de modifier les différents paramètres de votre projet sauf qu’il faut avant tout faire des références aux envrionnements créés précédemment.

Donc rendez-vous dans le deuxième panneau References. c’est dans ce panneau que vous allez donner les différentes envrionnement de configuration.

Note : On peut directement créer des environnements via ce panneau.

Par la suite vous pourrez utiliser vos variables d’environnement dans le premier panneau.

Les ConnectionString

Comme vous pouvez voir dans mes screenshots, il y a un onglet dédié aux Parameters et un autre pour le Connections Managers.

Vous pouvez voir dans l’onglet parameters que j’ai paramétrer une connectionString et que l’on peut faire de mêne dans l’onglet Connection Managers.

Quelle approche prendre, paramétrer toutes les connectionString via un paramètre où modifer les connexions dans l’onglet Connection Managers ?

Réponse : les deux sont possibles à vous de voir vos connexions en fonction de votre besoin et d’ajuster votre configuration. Au final on reviendra à la même chose, une variable d’un de vos environnements peut changer les valeurs. Sauf que d’un côté une connectionString à changer et de l’autre, 6 propriétés (ConnectionString,InitiaalCatalog, Password,RetainSameConnection,ServerName,UserName).

Note: On peut toujours donner une valeur manuelle sans passer par des variables d’environnement.

Dans mon cas, la connectionString de ma source ne change pas mais je l’ai tout de même mis en paramètre, si il faut la changer un jour je peux le faire… Par contre mes connexions de destination sont variables car tout dépend de l’environnement.

Je vous conseille très fortement  de faire une validation de votre configuration : Clic droit Validate sur votre projet.

Conclusion

La prise en main de ce nouveau modèle de déploiement parait complexe au premier abord. Cela est dû je pense à plusieurs choses.
Dans un premier temps les appellations : « variables, paramètres, variables d’environement, configuration etc. » qui naturellement quand on l’entend nous fait penser à des choses existantes, par conséquent on peut être facilement confus, mêlé voir perdu … Un bon départ est d’oublier un peu ce que l’on sait et reconstituer le tout petit à petit en mettant les nouvelles choses au bon endroit.

La découverte de ce modèle me laisse penser qu’on a plus de flexibilité et permet à tous les intervenants d’un projet d’intégration de données avec SSIS, d’avoir leur part du travail dans de meilleurs conditions. Dans un deuxième temps, étant habitué à développer et à déployer de la même manière depuis un petit moment déjà, il est difficile d’absorber ce modèle. Mais les nouveaux réflexes s’apprennent vite et au fure et à mesure on peut se dire que  » ce truc est quand même pas mal fichu et qu’on galère moins qu’avant… » Pour l’instant c’est sûre que l’on est en période de changements, les premiers projets avec SQL Server 2012 pointent leur nez sans conpter que SSIS 2012 réserve encore pas mal de suprises je pense notamment au reporting intégré et une meilleure intégration des projets avec TFS.

Déploiement package SSIS 2008 R2 File System + Fichier de configuration + SQL Agent

Un article pour parler d’une chose assez abstraite (surtout lorsque l’on débute SSIS) : Le déploiement de SSIS.

Il existe différentes méthodes de déploiement avec SSIS 2008.

Pour infos la version 2012 offre une toute nouvelle approche.

Alors en 2008, le déploiement des packages SSIS peut paraitre farfelu.

Déjà il faut choisir un mode File System ou MSDB (Déploiement fichier ou base de données).

Je ne vais pas débattre sur le choix le plux optimisé. Tout dépend des organisations et à la bonne volonté de chacun.

Après il faut aussi penser à la configuration des packages :

  • Fichier Configuration
  • Variable d’environnement
  • Configuration Indirecte

Dans différents projets utilisant le mode File System et la configuration indirecte, il y a toujours pas mal d’interrogations.

Je vais essayer de tout expliquer … Retenez que tout est une histoire de contexte.

Développement des packages

Commencons par le début : le développement des packages. Le développeur travaille sur son poste, il cré des packages à la pelle. Il utilise des comptes pour se connecter aux sources et aux destinations. Un genre de DOMAIN\etl_user pour les sources : qui ne fait que lire la ou les différentes sources. Et ce même compte pour manipuler les données dans la destination / Staging etc.

Le développeur possède un compte active directory, DOMAIN\IamTheDev. Si ses packages utilisent divers fichiers de configuration DtsConfig (indirect ou direct) alors ils doivent contenir les ConnectionString vers les sources et les destinations. Qui dit ConnectionString dans un fichier de configuration dit : identifiants pour accéder aux données = DOMAIN\read_user + Mot de passe. Et oui ces informations sont stockées en claires dans le fichier.

Lorsque le développeur va exécuter un package dans BIDS, ce dernier étant configuré, va utiliser toutes les information de connexions contenues dans le fichier de configuration pour se connecter aux sources et aux destinations. Vu que le développeur possède les droits sur le fichier de configuration alors celui va être lu et utlisé dans ce contexte (exécution par le développeur sur son poste).

Le mode/type de déploiement déjà défini préalablement le développeur sait que l’on va déployer en file system et utiliser un fichier de configuration.

Sauf qu’à la création d’un package celui ci possède un ProctectionLevel qui par défaut est mis à EncryptSensitiveWithUserKey. Ce qui signifie que les informations du package sont encryptées avec les informations de la personne qui a créé le package, dans notre cas le développeur. Donc si on laisse cette propriété par défaut, et que l’on souhaite exécuter ce package ailleurs par un autre utilisateur ou  sur notre serveur SSIS il y aura à coup sûre un message d’erreur.

Par conséquent il faut changer le protectionLevel de vos packages : DontSaveSentive ou SaveSensitiveWithPassword.

Le déploiement en file system est assez simple:

  • Un bon vieux copier coller des packages
  • Utilisation du DeploymentManifest
  • Autres ?

Vers le dossier contenant les packages sur votre serveur. Idem pour les fichiers de configurations.

Exécution et planification des packages

Pareil plusieurs manières d’exécuter un package SSIS. (Ligne de commande DTUTIL, DTEXEC et SQL Agent) Concentrons nous sur le service SQL Agent, il  doit être lancé dans un premier temps. Dans SQL Agent, un job devra être créé. Ce job contiendra votre package SSIS.

Sauf que…

Je vais vous ai dit tout en haut de cet article que tout est une histoire de contexte. A coup sûre tout ne fonctionnera pas du premier coup. Surtout si vous avez des bases de données externes de votre serveur SSIS.

Pour bien exécuter un package, il faut comprendre que celui va appeller différente sources données. Et que le service SQL Agent de base ne pourra pas forcément y accéder.

C’est pour cela qu’il faut utiliser un proxy. Et un un proxy nécessite un credential qui est lui même lié à un compte active directory. : DOMAIN\etl_user

Un bon schéma vaut mieux qu’un long discours !

Retour – Certifications SQL Server 2012

Bien que SQL Server 2012 soit officiellement sorti ses certifications sont encore en BETA.

Ayant profité de l’offre gratuite de passage de certifs, j’ai pu testé ces nouveaux examens.

Merci Christian pour le bon plan : http://blogs.codes-sources.com/christian/archive/2012/03/23/certifications-sql-server-2012-alias-denali-disponibles-gratuitement-en-beta.aspx

Alors autant vous dire tout de suite que je me suis rendu dans le centre de test sans vraiment préparer. Je comptais essentiellment sur ma veille technologique, les conférences, Webcasts et articles que j’ai pu faire, assister et lire. Je n’espère pas grand chose…

Voici la liste des certifications SQL Server 2012, et la combinaison d’exam forment soit la stack Data Platform ou la stack Business Intelligence.

  • Exam 70-461: Querying Microsoft SQL Server 2012
  •  Exam 70-462: Administering Microsoft SQL Server 2012 Databases
  • Exam 70-463: Implementing a Data Warehouse with Microsoft SQL Server 2012
  • Exam 70-464: Developing Microsoft SQL Server 2012 Databases
  • Exam 70-465: Designing Database Solutions for Microsoft SQL Server 2012
  • Exam 70-466: Implementing Data Models and Reports with Microsoft SQL Server 2012
  • Exam 70-467: Designing Business Intelligence Solutions with Microsoft SQL Server 2012

Au programme, sur les 7 certifications SQL Server 2012 je me suis inscrit à 3 d’entre elles le même jour :). (Une bonne piqûre !)

  • Exam 70-463: Implementing a Data Warehouse with Microsoft SQL Server 2012
  • Exam 70-466: Implementing Data Models and Reports with Microsoft SQL Server 2012
  • Exam 70-467: Designing Business Intelligence Solutions with Microsoft SQL Server 2012

Je me suis attaqué aux certifs de la stack Business Intelligence,  il faudrait ajouté 70-461 et  70-462 pour avoir la totale.
Avant d’y aller, j’ai jeté un oeil sur le contenu des certifications :

Contrairement aux certifications 2008 on a vraiment plus de certifications et on voit bien que la BI prend vraiment un sens au niveau des compétences SQL Server.
En effet les composantes BI et les domaines de compétences sont mieux dissociés au lieu d’avoir 2 certifications BI regroupant SSAS, SSIS, SSRS et un peu de reste  (comme c’était le cas pour 2008) on a désormais 3 certications qui partagent un peu les 3 grandes familles d’outils BI.

Je ne vais pas tout détailler, mais globalement les questions sont bien posées et maintenant elles sont interractives.

Fini les textes très long à lire on a des screenshots interractifs, des drag and drop des étapes à mettre en place pour répondre à une question, je vous rassure les QCM sont encore là.

La 70-463 est intéressante enfin des mises en situation de  data warehouse (modélisation, chargement etc).

La 70-466 : Plutot dure, n’étant pas un Master du MDX et de l’optimisation de cube, et ne connaissant pas tous les secrets du BISM : Multidimensionnal VS Tabular, j’avoue avoir répondu un peu au hasard.

La 70-467 : est un peu équivalente à la 70-452 de SQL Server 2008. Elle regroupe une peu tout est plus plus orienté cas de tout les jours.

Les 3 ont a peu près 60 questions chacune

Toutes les nouveautés sont couvertes dans différentes proportions : on parle de SSDT, SQL Azure, DQS, MDS, PowerPivot, PowerView, ColumnStore, Nouveautés SSIS en veux tu en voilà, Modèle Tabulaire, DAX.

Cela reste des BETA, tout peut encore arrivé.

Et pas mal de question en référence à 2008 aussi.

Certaines questions sont vraiment pointilleuses, d’autres sont des « par coeur », il y en a des fastoches TAC-O-TAC et par rapport aux certifications 2008 il y avait des questions toutes simples mais Dieu sait qu’on a la réponse mais on ne sait plus comment s’appelle ce truc, on l’utilise tous les jours mais on ne fait plus attention au nom de cette propriété, de cette tâche, composant ou onglet. (GRRrrr)

A croire les feuilles imprimées en sortant résultat par mail dans 8 semaines.

SSIS – Charger une dimension hiérarchique style Employee (Initial, Incrémental, SCD Type 2)

Récemment on m’a demandé de me pencher sur ce cas. En prenant en compte certains pré requis

Le tout consiste à charger une dimension Dim_Employees avec prise en compte du Slowly Changing Dimension de Type 2. Le SCD s’active lorsque le manager change.

Considérations de l’exercice.

  • Utiliser le plus possible SSIS
  • Prendre en compte le composant SCD Natif
  • Le volume de données est faible (de l’ordre du millier)
  • Ne pas changer la structure des tables
  • Pas de tables temporaires

Ci-dessous la structure des tables. Rien d’extraordinaire, noté juste la présence des clés (EmployeeKey et ParentEmployeeKey) de la source dans la destination. Elles sont préfixé par src_ .

Comment on charge  ?

 » On charge les managers puis les autres » –> Bouing !
 » On charge les employées puis les managers » –> Presque !

Une voir LA réponse : On charge tout le monde sans les managers puis on affecte les managers.
La colonne manager acceptant les valeurs NULL on peut la laisser sans valeur.

Note : Dans le cas ou cette colonne était NOT NULL il aurait fallu mettre une valeur par défaut comme -1. Cela voudrait dire que chaque employé possède un manager, et voir la possibilité qu’un employé soit manager de lui même si il n’a pas de manager. Bref…

Le traitement s’effectuera en deux passes, on charge tous les employés (peu importe qu’ il soit manager ou pas) puis on assignera les managers via un Update.
Mais….
il faut prendre en compte du SCD sur le manager, si on fait notre UPDATE alors le  SCD va s’activer, cela aura pour conséquence de créer une nouvelle ligne. Oui c’est vrai mais ne l’oublions pas on a toujours un scénario de chargement Initial et un scénario Incrémental.

En effet lors du chargement initial le SCD ne sera pas appelé, par contre dans l’incrémental il faudra l’utiliser.

Ce qui donne un package de ce genre :

Explication rapide :
Le composant  SQL_Count_Row va faire un COUNT sur la table de destination et stocker la valeur du count dans une variable SSIS. Il y a des vérifications par la suite si le Count retourne 0 alors c’est un chargement Initial si le count est supérieur à 0 alors on est dans un chargement Incrémental.

Note : Dans un développement ETL avec SSIS, plusieurs manières de gérer le chargement initial et le chargement incrémental. On aurait pu développer deux packages différents pour chaque scénarios : Un package initial et un package incérmental. On aurait alors soit un package Master pour chaque type de chargement soit un seul package master et des tables de configurations pour gérer les types d’exécution des packages.

Chargement Initial

Le Sequence Container SEQ_Initial_Load, contient deux tâches de flux de données.

Le premier (DFT_Load_All_Employees) est tout simple il insère les employés en laissant la colonne Manager_Id à NULL.

Le second (DFT_Affect_Manager_To_Employee) est un peu plus complexe. Il y a un double Lookup pour assigner le bon Manager_Id en se basant sur le nouvel Employee_Id généré par la table.

Détail du premier Lookup (LKP_Employeed_Id)

Détail du second Lookup (LKP_Manager_Id)

Etat des données :

A la source :

Après l’exécution du DFT_Load_All_Employees

Après l’exécution du DFT_Affect_Manager_To_Employee

Chargement Incrémental

Si on exécute le package par la suite il ira dans la branche incrémentale.

Explications :

Le premier lookup (LKP_DESTINATION) va juste vérifier si l’enregistrement existe. Il va comparer les clés de la source avec la clé naturelle de la destination. Si il n’y a pas de correspondance alors c’est une insertion d’un nouveau record (DST_Insert_New_Employees).

Note du 7 Mars : Après revisite de la solution il manque un lookup avant d’insérer un nouvel enregistrement. Ce lookup va récupérer l’ID du manager un peu comme dans le chargement initial.

 Si il y a correspondance alors l’enregistrement existe dans ce cas là on laisse le SCD gérer le changement.

Configuration du composant SCD_Manage_Data :

Le dernier lookup (LKP_Manager_Id) est nécessaire ici car il re assigner le nouvel ID du manager en récupérer son Employee_ID.

Détail du Lookup :

Note : La sortie du composant SCD New Output peut être supprimé elle ne sert à rien étant donné que le premier lookup gère les nouvelles entrées.

Test

On change les données de la source.

On exécute le package.

Résultats dans la destination :

Conclusion

Un article assez long pour un sujet sensiblement facile aux premiers abords.

Le chargement de données dans un entrepôt de données est un art, pour réaliser cette oeuvre il faut jouer de différentes techniques.
La prodédure décrite dans cet article permet de remplir les objectifs dans un certain contexte. Différents facteurs peuvent intervenir et de ce fait l’approche peu changer. Tout de même certains principes demeureront à jamais. Un data warehouse connait un chargement Initial et un chargement incrémental, il faut donc adapter le développement pour chacune de ces phases. Les problématiques et les questionnements peuvent trouver leurs solutions tout simplement en leur mettant dans le contexte associé.

SQL Server 2012 – Nouveautés Business Intelligence

Récemment j’ai dû écrire un article concit concernant les nouveautés BI de SQL Server 2012. Un discours mi-technique mi-descriptif que vous trouverez juste en dessous…

Pour rappel j’ai publié plusieurs articles concernant :

La base de données SQL Server de Microsoft connaitra une nouvelle version majeure : SQL Server 2012.

Tout au long de son histoire, cette base de données a évolué et depuis quelques années elle est devenue bien plus qu’un simple moteur de base de données… SQL Server est plus que jamais une plate-forme complète d’analyse de données d’entreprise. Cette version 2012 vient étoffer cette offre en apportant un lot d’améliorations et de nouveautés.

Cet article vous dévoilera les nouveautés Business Intelligence de SQL Server 2012.

La BI connaitra une riche année 2012

La partie BI de SQL Server 2012 connait un bon nombre de nouveautés et Microsoft a même créé une nouvelle édition spécialement dédiée à l’intelligence d’affaire : SQL Server 2012 Business Intelligence.

La naissance d’un nouvel outil : Power View

La famille de Reporting Services s’agrandit et accueille un nouvel outil : Power View. Cet outil mêle reporting ad-hoc et visualisation de données, ce qui offre une toute nouvelle expérience utilisateur. Techniquement, Power View permet de manipuler des données dans une interface Silverlight intégré à SharePoint.

Le principe est simple, on se trouve face à une feuille blanche sur laquelle on dépose différentes données provenant d’une liste de tables. Cela  va créer un tableau et grâce à cet outil on est capable de transformer ce tableau en visualisations. Parmi les nombreuses visualisations on trouve un graphique à bulles qui animera vos données sur un axe des temps.
Power View s’appuie sur la nouvelle couche sémantique BI et utilise un nouveau modèle de données de type tabulaire, utilisé par Power Pivot, et désormais greffé à Analysis Services (SSAS).
Un outil à voir : http://www.youtube.com/watch?v=75szAtMrkNs

Le nettoyage de données : un sujet important et un service dédié

Data Quality Services (DQS) est un nouveau service de SQL Server 2012. Il vous permettra de nettoyer vos données en faisant participer les responsables de données, les personnes métiers et le SI.
Par le biais d’une interface cliente il sera possible de créer des bases de connaissance. Ces bases contiendront vos règles d’épuration, de validation ainsi que vos données de référence. DQS vous épaulera également dans la recherche de correspondance (Matching/Doublons) et dans l’évolution de vos bases de connaissance.
Les différentes règles de nettoyage pourront être utilisées au niveau les flux de données dans Integration services (SSIS) via un nouveau composant.
Concernant SSIS, ce dernier connait des améliorations qui simplifieront le développement de flux de données ainsi que le déploiement des solutions ETL dans différents environnements.

Autres nouveautés

SQL Server s’ouvre davantage aux services dans cloud en donnant la possibilité d’étendre vos analyses/nettoyages/références avec des données du SQL Azure DataMarket. https://datamarket.azure.com/browse/Data/

Reporting Services (SSRS) connait deux changements marquants. Tout d’abord son intégration dans SharePoint est simplifiée. Puis SSRS possède maintenant un système d’abonnement plus efficace et mieux adapté aux besoins des consommateurs de rapports. Il est dorénavant possible de créer des règles qui alerteront vos usagers en fonction du mouvement de vos données dans vos rapports.

Pour terminer les outils des utilisateurs finaux appartenant à pile BI Microsoft viendront compléter l’artillerie d’analyse des données : Excel + Power Pivot 2.0 + Mater Data Services.

Les licences et prix

Microsoft profite de cette nouvelle version pour simplifier les licences en proposant uniquement 3 éditions principales.

  • SQL Server 2012 Entreprise
  • SQL Server 2012 Business Intelligence
  • SQL Server Standard

Les prix sont aussi revus, désormais le coût des licences dépendra du nombre de cœurs de vos serveurs et non plus du nombre de processeurs.

Plus de détails : http://www.microsoft.com/sqlserver/en/us/future-editions/sql2012-editions.aspx

Avant d’entreprendre de nouveaux défis avec SQL Server 2012 il faudra être patient, car elle devrait être disponible dans les premiers mois de la nouvelle année. En attendant vous pouvez toujours essayer la version RC0 disponible sur le site de Microsoft : http://www.microsoft.com/sqlserver/en/us/future-editions.aspx

SSIS – Charger des données dans des tables liées

Un titre très vaste pour un sujet très commun.

En effet il arrive souvent (très très souvent) qu’on doit alimenter des tables.
Il arrive de temps en temps (voir tout le temps) que ces tables soient reliées à une ou plusieurs tables.

Les relations entre ces tables s’appuient sur des contraintes de type clé étrangère. Donc si on alimente une table qui possède une clé étrangère on peut rencontrer des erreurs à cause de cette contrainte.

Une erreur du genre :

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_XXX_YYY"

Comment fait on pour pallier à ce problème de contraintes dans SSIS ?

Se poser des questions

Avant de se lancer dans le développement il faut se poser des questions:

  • Quelle la nature des données ?  Données critiques ou non critiques ?
  • Quelle est le type de ma destination ? Dimensions ? Table de faits ? Tables d’une base de données classique ? Tables externes/références ? Tables de Staging ? Tables Temporaires ?
  • Quel est l’importance de l’intégrité de ces données ?
  • Il y a t’il une logique de chargement ? L’ordre d’exécution de mes flux de données est il important ?
  • Est ce que cela doit prendre du temps ?
  • Est ce que je peux toujours changer ce qui a été fait pour m’adapter aux changements ?
  • Quelles sont les politiques et les pratiques actuelles concernant la gestion des données ? Et ma méthode sera t’elle adoptée ou rentre elle en conflit avec ce qui est déjà établi ?

Que de questions …

Les méthodes

Plusieurs techniques existent. Cet article n’a pas pour but de dévoiler la méthode à privilégiée, mais il vous présente les différents école de pensées.

A vous de faire un choix dépendant de la situation rencontrée.

Méthode 0

 « Selon la théorie, si j’ordonne mes flux tout ira bien »

Dans un monde parfait, les données issues de vos systèmes sources sont entièrement intègres. Par conséquent si l’on charge en premier la table de référence puis la table reliée à cette table de référence,
logiquement, vous n’aurez pas de problème.
Exemple : Une table Sous Catégorie reliée à une table Catégorie.
Si on charge toutes les Catégories dans un premier temps puis on charge les Sous Catégorie.

Sauf que dans le monde de la BI, les données ne sont pas si parfaites que ça  surtout quand vous intégrez plusieurs sources de données. Il se peut qu’une sous catégorie ne fasse pas du tout référence à une catégrorie.

Avantages :

  • Exécution logique voir naturelle
  • Principe d’ordonnancement des flux : l’ordre d’exécution des flux  est une notion fondamentale

Inconvénients :

Cette méthode est purement théorique, la pratique est une triste réalité. Dans un environnement où on intègre des données diverses et variées,  il est très propable de rencontrer des exceptions.

Méthode 1
« La contrainte nous dérange… Bah on l’enlève ! Et on la remet après »


Avec des composants Execute SQL de chaque côté de votre composant Data Flow Task.
Le premier Execute SQL va forcer le fait qu’on ne vérifie pas les contraintes via un ALTER TABLE  :

ALTER TABLE  SubCategory NOCHECK CONSTRAINT FK_SubCategory_Category

Le second Execute SQL va quant à lui  ré appliquer la contrainte après le chargement des  données.

ALTER TABLE SubCategory WITH CHECK CHECK CONSTRAINT FK_SubCategory_Category

Avantages:

  • Fonctionne bien et on contrôle ce que l’on fait.
  • Pas besoin d’ordonnancer vos flux.

Inconvénients:

  • Il faut connaitre les noms des contraintes
  • Il faut avoir les droits nécessaires sur les tables et pas sûre que l’idée emballe tout le monde.
  • Petite porte ouverte aux erreurs d’intégrités ?
  • Maintenance : Si on a plusieurs FK et que d’autre FK s’ajoute avec le temps

Méthode 2
« La contrainte nous dérange ? Heu non pas tout à fait, on peut passer à travers … »


Lorsque l’on utilise un composant OLE DB Destination, on a la possiblilté de décocher le Check Contraints.

Avantages:

  • Rapide et simple
  •  Pas besoin d’ordonnancer vos flux.

Inconvénients:

  • Grandes Portes ouvertes aux erreurs d’intégrités ? Désactive toutes les contraintes.
  • Fonctionne uniquement avec les destinations OLE DB en mode FAST LOAD (certes le mode le plus utilisé)

Méthode 3
 » On vérifie qu’il y a une correspondance dans la table de référence puis on charge si c’est vérifié »

C’est ce que l’on fait typiquement pour charger une table de faits. La vérification se fait grâce à un composant Lookup. En temps normal on a autant de LOOKUP que de FK.


Avantages:

  • Sagesse et Sécurité
  • Détections des erreurs

Inconvénients:

  • Le chargement doit être ordonné.
  •  Dépendance au composant Lookup qu’il faut savoir gérer (performance, optimisation…)

Conclusion

En fonction des cas et des situations que vous rencontrez, l’intégration de données dans des tables liées peut varier. Plusieurs critères sont à prendre en compte afin d’adopter une méthode adéquate.

Bien évidemment il n’est pas possible de prévoir tout les scénarios possibles, n’hésitez pas à partager vos points de vue.

SSIS CheckPoints un exemple rapide

Présentation

/!\  L’utilisation des Checkpoints n’est pas recommandée /!\

SSIS propose de faire des checks points qui vous permettront de relancer votre processus d’ETL là où il s’est arrêté à cause d’une erreur.

Supposons que dans votre Control Flow, vous possédez 7 Data Flow.

Votre processus ETL se passe à merveille et une erreur survient au 6ème  data flow.

En sachant qu’il a fallu attente 4 heures pour arriver à ce 6ème data flow.
En temps normal vous regardez l’erreur que SSIS vous retourne, vous la corrigée.
Et vous relancer votre package et c’est reparti pour 4 heures … et cela peut encore planter toujours sur le 6ème ou sur les Data Flow suivants…
Les check points sont là pour faire repartir votre processus ETL depuis le point d’erreur.
Donc repartir de votre 6ème Data Flow directement et épargner des heures d’attente.

Action

Comment mettre en place les checkpoints ?

Tout d’abord il faut savoir que les Checkpoint sont utilisables dans le Control Flow uniquement.

Au niveau de votre Control Flow,
il faut changer 3 propriétés de votre package :

  • CheckPointFilename
  • CheckPointUsage
  • SaveCheckPoints

Globalement, le checkpoint est un fichier qui va être créé sur votre système, SSIS relancera votre package si ce fichier existe il prendra en considération votre Checkpoint.

L’étape suivante consiste à modifier la propriété FailPackageOnFailure à True sur les composants de votre Control Flow.

Illustration

Control Flow avant l’exécution

Exécution du package et Erreur sur le 6ème Data Flow, le fichier checkpoint est généré sur votre système.

On corrige l’erreur et on relance l’exécution du package, ce dernier reprend au niveau du 6ème package.

Voici ce que contient un fichier checkpoint :

Commentaires

Il est bien de savoir que l’option existe maitenant à son utilisation…

/!\  L’utilisation des Checkpoints n’est pas recommandée /!\

Créer une Time Dimension avec SSIS

Une Time Dimension contient toutes les données d’une période définie, c’est une dimensiion plutôt stable car elle ne connait aucun changement.
Un seul chargement de données est suffisant pour alimenter cette dimension.
Il est très important de fixer au départ la granularité (Année, Trimestre, Mois, Jour etc…). Et rien ne sert d’alimenter la dimension temps avec une dizaine d’années …il est préférable de chager 2 à 3 années et de rajouter au moment voulu les  années futures.
La dimension Temps est une dimension incontournable dans un data warehouse. On peut générer cette dimension de différentes manières:

–>Option 1 : Utilisation de l’assitant de création de dimension dans SSAS.

Cet assistant à l’avantage d’être simple, rapide  et il gère différents types de calendriers.
L’inconvénient majeure c’est qu’il n’est pas très pratique pour le choix du formatage et des langues.
Il est conseillé d’utiliser cet assitant dans le cadre d’une preuve de concept…

–>Option 2 : Créer un script SQL

CREATE TABLE Dim_Time_Generated 
( 
DateID       INT        NOT NULL    PRIMARY KEY 
,MonthName    VARCHAR(9) NOT NULL 
,MonthNumber  INT        NOT NULL 
,DayNumber   INT        NOT NULL 
,DayName      VARCHAR(9) NOT NULL 
,Quarter      INT        NOT NULL 
,QuarterName  CHAR(7)    NOT NULL 
,Year         INT        NOT NULL 
,FullDate DATETIME NOT NULL 
)
BEGIN 
SET DATEFIRST 1 
DECLARE @startDate  DATETIME 
DECLARE @endDate    DATETIME 
DECLARE @date       DATE 
DECLARE @DateId  INT 
DECLARE @cpt INT 
SET @startDate  =    '2009-01-01' 
SET @endDate    =    '2009-12-31'
SET @cpt        =    0 
SET @date       =    DATEADD(dd, @cpt, @startdate)
WHILE    @date <= @enddate 
BEGIN 
SET @dateId = CONVERT(VARCHAR(8), @date, 112)
INSERT INTO Dim_Time_Generated(DateID,MonthName,MonthNumber,DayNumber,DayName,Quarter,QuarterName,Year,FullDate)
VALUES ( @DateID 
,DATENAME(mm, @date)
,DATEPART(mm, @date)
,DATEPART(dd, @date)
,DATENAME(DW, @date)
,DATEPART(qq, @date) 
,'Q' + DATENAME(qq, @date)
,DATEPART(yy, @date)  
, @date )
SET  @cpt   =    @cpt + 1 
SET  @date  =    DATEADD(dd, @cpt, @startdate) 
END
END

Les scripts SQL permettent d’avoir plus de liberté et de contrôle sur les données.

–>Option 3 : Utiliser des générateurs de temps

Il existe des générateur de script SQL pour créer votre dimension des temps comme ce site : http://www.regnecentralen.dk/time_dimension_generator.html

–>Option 4 Utiliser SSIS

Il est possible d’utiliser SSIS pour créer sa propre Time Dimension.
Voici une dimension Temps très simple dans un data warehouse.

Dans un projet SSIS on créé les variables suivantes :

StartDate et EndDate : ces variables vont spécifier la période de temps que l’on souhaite avoir dans notre dimension.
MonthName et DayName sont des variables SSIS, elles vont être utilisées car SSIS ne possède pas de fonction pour récupérer directement le nom du mois et le nom du jour.

On créé un Dataflow, qui possèdera cette structure:

Le script C# est de type Source. Il nous permettra de récupérer toute les dates de la période spécifiée mais aussi le nom des mois et des jours.
Dans l’éditeur du composant script on ajoute les 4 variables SSIS dans la propriété:ReadOnlyVariables.


Puis on créé les sorties comme ceci:

On peut maitenant éditer le code C# du composant script, plus précisément la méthode CreateNewOutPutRows() :

 public override void CreateNewOutputRows()
 {
 /*
 Add rows by calling the AddRow method on the member variable named "<Output Name>Buffer".
 For example, call MyOutputBuffer.AddRow() if your output was named "MyOutput".
 */
 DateTime tmp = this.Variables.StartDate;
 do
 {
 TimeOutputBuffer.AddRow();
 TimeOutputBuffer.FullDate = tmp; //Récupération des dates au format MM/JJ/AAAA
 TimeOutputBuffer.MonthName = tmp.ToString("MMMM"); //Le nom du mois (Janvier,Février...)
 TimeOutputBuffer.DayName = tmp.ToString("dddd"); //Le nom du jour (Lundi, Mardi Mercredi ...)
 tmp = tmp.AddDays(1);

 } while (tmp <= this.Variables.EndDate);
//NOTE IMPORTANTE:
//Les fonctions utilisées retournent des valeurs dans langue utilisée par le système.

On utilise un composant Derived Column pour créer les autres champs nécessaires

Maitenant il ne reste plus qu’à mettre une destionation OLE DB vers votre dimension de votre Data warehouse.
Dans notre cas j’ai utilisé un Union All et un activer un Data Viewer pour vous montrez le résultat.

Change Data Capture et SSIS pour un load intelligent

Présentation

Ce projet de chargement intelligent des données repose sur l’utilisation du Change Data Capture de SQL Server 2008 / 2008 R2 et la mise en place d’un historique de données grâce aux dimensions à variation lente de type II (Slowly Changing Dimension (SCD)  Type II).

L’idée de départ…

Je possède une base de données source avec une table contenant les informations de mes clients.  Et je souhaite utiliser certaines de ces informations pour alimenter la dimension associée dans mon data warehouse.
Dans un premier temps il faudrait pouvoir charger toutes les données directement  dans la dimension.
Par la suite il faudrait charger uniquement les modifications.
Le package qui assurera ce rôle devra être « intelligent », il pourra automatiquement basculer entre un scénario de premier chargement (First Full Loading)  ou un chargement incrémenté prenant uniquement  en compte les mises à jours (Incremental Loading).
De plus ce package serait modulaire, on pourrait le réutiliser sur d’autres tables associées à d’autres dimensions: Il suffirait juste d’initialiser certains paramètres.
Et bien évidement cette méthode devrait être performante et simple de maintenance.

Techniquement parlant…

Il existe différents moyens de gérer l’alimentation des dimensions d’un data warehouse.
Les « upserts » consistent à mettre à jour les données et/ou insérer de nouvelles données.
Avec SSIS différentes méthodes existent pour traiter ce type de cas. On peut utiliser des « Lookups », des « Merges » et pour gérer l’historique les « Slowly Changing dimensions » (SCD) de type II.
Une tout autre approche est possible, en utilisant les fonctionalités liées directement à la base de données SQL Server: le Change Data Capture (CDC).
Le Change Data Capture est une fonctionnalité qui lorsqu’elle est activée permet de récupérer uniquement les données modifiées d’une table. Les modifications enregistrées  sont les insertions,  les mises à jour et les suppressions de données (INSERT/UPDATE/DELETE).
Le CDC s’active tout d’abord sur une base de données puis sur une table . L’activation du CDC va engendrer la création d’une table système qui stockera toutes les modificaitons. En consultant cette table il sera possible d’alimenter une dimension d’un data warehouse en prenant uniquement en compte les modifications des données.
Le diagramme ci-dessous explique brièvement les différentes étapes que devra assurer le package.

Explication rapide

Au lieu d’activer nous même le CDC sur les sources de données, le package s’en occupera automatiquement.
Lors de la première exécution du package, si le CDC n’est pas activé alors le package chargera l’ensemble des données dans la dimension. A la fin de ce chargement le package activera le CDC sur la la base de données mais aussi sur la table source.
A partir de ce moment bien précis toute les futures modifications de cette table seront tracées. Lors de  la prochaine exécution du package ce dernier s’occupera uniquement des modifications référencées par cette table CDC.
Toute « l’intelligence » de cette méthode repose sur la détection du CDC, de son activation automatique et du chargement de données incrémenté des données modifiées depuis la dernière exécution du package.
Mais aussi la configuration des paramètres qui va permettre la modularité de la solution.

Pré requis

L’agent SQL Server doit être démarré.

Puis exécuter ce script  dans votre data whareouse .

USE YOUR_DWH
GO

CREATE TABLE dbo.LastExecution
(
 LastExecution DATETIME NOT NULL
)

GO
INSERT INTO LastExecution VALUES ('2000-01-01')

Ce script va créer une table qui stockera uniquement la derniere date d’éxécution du package.

Ce projet est un exemple pour vous montrer ce dont on est capable de faire. Il se peut que le cas présenté ici ne colle pas directement  aux réalités que vous pouvez rencontrer.
Dans notre scénario voici la table source :

Très important le CDC ne peut être activé que sur une table possèdant une PK ou une colonne IDENTITY.
La dimension de destination dans le data warehouse :

Le but est de créer un historique dans le data warehouse, on utilise donc le principe des SCD de type II c’est pour cela que les champs StartDate, EndDate et RecordStatus sont importants.

Comprendre comment ça marche

Le projet est composé de 3 packages :
• Initialization
• First Full Loading
• Incremental Loading

Package Initialization

Le Package Initialization est le package de départ, il joue le rôle de package parent. Il transmet aux package enfants ( First Full Loading et Incremental Loading) les paramètres et les variables nécessaires pour le bon  exécution des chargements de données.

Ci-dessous la liste des variables :

• SOURCE_DB : Nom de la base de données source opérationnelle

• SOURCE_TAB : Nom de la table source opérationnelle

• SOURCE_SCHEMA : Nom de votre schéma de votre base de données opérationnelle

• FILEGROUP : Nom du fichier de groupe de la base de données opérationnelle

• CDC_ROLE : Le Nom du role qui exécutera le Change data Capture

• DESTINATION_DIM : Dimension de votre Data Warehouse

Ce composant vérifie si le CDC est activé sur la base de donnée source opérationnelle (SOURCE_DB) via la requête :

SELECT  is_cdc_enabled AS 'CDC_INFO'
FROM sys.databases
WHERE name = ?
Cette requête interroge une table système et vérifie si le Change Data Capture est activé sur la base de données source que l’on a défini dans la variable SOURCE_DB.

Le résultat est contenu dans la variable CDC_ENABLED que l’on mappe à CDC_INFO.

Lorsqu’on exécute ce package pour la première fois ce dernier  va remarquer que le CDC n’est pas activé sur la base de données source… le package First Full Loading va s’exécuter par la suite.

Si le CDC est activé sur la base de donnée source alors c’est le package Incremental Loading qui s’éxécutera.

Package First Full Loading

Le package First Full Loading va charger tous les données existantes de table source opérationnelle vers la dimension du data warehouse. A la fin de ce flux de contrôle il activera le CDC sur la base de données et sur la table source.
Durant son exécution ce package mettra à jour la valeur LastExecution de la table LastExecution.

Ce package contient un bon nombre de variables la plupart prennent la valeur des variables parents du package Initialization :  toutes les variables qui sont préfixées par « INIT_ »

Pour transférer les valeurs entre deux variables de type parent/enfant , il suffit de paramétrer la configuration du package en utilisant le type « Parent package variable ».

Flux de données  : FFL-Dim_Customers

OLE DB Source : SOURCE_DB_TAB

Tout  d’abord, il faut créer votre connexion vers votre base de données source. Par la suite il faut utiliser cette connexion sur dans le composant source OLE DB via le « Advancedd Editor »   dans l’onget « Connection Manager ».

Dans l’onglet « Component Properties », les propriétés  ValidateExternalMetada, AccessMode et SqlCommandVariable sont définies comme ci-dessous.
Ces propriétés sont nécessaires pour rendre la solution SSIS modulaire.
ValidateExternalMetada empéchera au composant de retourner une erreur suite aux vérifications des métadonnées. Si on laisse cette propriété a True le composant sera marqué comme erroné.
Mais si on exécute le package tout se déroule correctement.
La variable User ::GetData contient la requête SELECT qui va récupérer les champs que l’on désire insérer dans le data warehouse.
GetData est définit comme ceci :
La propriété EvaluateAsExpression doit être à True car la requête SELECT est basée sur une expression.
Etant capable de définir la table source  (variable SOURCE_TAB) depuis le package initialization on peut alors requêter dynamiquement n’importe quelles tables. La requête ajustera donc le FROM pour pointer sur la table désirée.

Récapitulatif.
Etape/Package/variable/Description
1 /Initialization/SOURCE_TAB/Définition de la table source
2/First_Full_Loading/INIT_SOURCE_TAB/Récupération du nom de la table source
3/First_Full_Loading/GetData/Utilisation de la variable INIT_SOURCE_TAB au niveau de la clause FROM

Le composant ne reconnaitra pas les champs d’entrées et de sorties il faut les créer à la main et spécifier correctement leur type.

Les champs d’entrées et de sorties sont disponibles dans l’onglet « Column Mapping » il vous suffit alors de les relier entre eux.

Colonne dérivée : Create new fields

La dimension du data warehouse posséde plus de champs que la la table source, cela est due aux colonnes liées à la gestion de l’horique avec les SCD de Type II.

StartDate fixe la date du status des enregistements, par défaut lors du premier chargement la date que j’ai choisi est celle de l’exécution du package.
RecordStatus représente le status de l’enregistrement, lors du premier chargement on force la valeur ‘OK’  qui signifie pour moi que les enregistrement sont valables.

OLEDB Destination : Destination_DWH_DIM

Comme pour le composant OLE DB Source, le composant OLDE DB Destination nécessite une configuration particulière.
1-Créer la connexion vers la dimension du data warehouse
2-Utiliser cette connexion dans le Connection Manager via l’Advanced Editor
Les propriétés du composant

La propriété AccessMode  est fixé à « OpenRowset Using Fastload From Variable »  la variable utilisé est « INIT_DESTINATION_DIM » qui contient le nom de la dimension de destination (Dim_Customers).

Retour au Control flow : Les SQL Task

Ce composant «Execute SQL Task » va mettre à jour la valeur de LastExecution de la table LastExecution. Cette table contiendra la date la plus récente du chargement de données. Cette date est importante pour la suite 🙂
 UPDATE LastExecution SET LastExecution=GETDATE()

Ces deux composants vont faire appels à deux procédures stockées pour activer le CDC sur la base de données en premier temps puis sur la table source
NOTE :Ne pas oublier d’uliser les bonnes connexions.
Active CDC on DB
 EXEC sys.sp_cdc_enable_db
Active CT on Table
L’activation du CDC sur une table nécessite des paramètres , on créé alors la requête via l’expression qui contituera la requete SQL.  Les variables utilisées dans cette requete sont présentes dans le package First_Full_Loading elles héritent les valeurs des variables parents du package Initialization.
"EXEC sys.sp_cdc_enable_table

 @source_schema = N'"+ @[User::INIT_SOURCE_SCHEMA] +"',

 @source_name   = N'"+ @[User::INIT_SOURCE_TAB] +"',
 @role_name     = N'"+ @[User::INIT_CDC_ROLE] +"',
 @filegroup_name = N'"+ @[User::INIT_FILEGROUP] +"',
 @supports_net_changes ="+ @[User::INIT_NET_CHANGES]

Package Incremental Loading

Ce package récupère la date du dernier chargement connue LAST_EXECUTION.
Puis il vérifie que le CDC est bien activé sur la table source. Si ce n’est pas le cas il lance le package First_Full_Loading.
Le flux de données présent va lire la table CDC associé à la table source et va récupérer les changements efffectués depuis la dernieère exécution du package.
Et pour finir on met à jour la valeur de last execution.

Execute SQL Task : SQL to LastExecution

Ce composant utilise une expression basée sur la propriété SqlStamentSource.
On fixe la variable @[User ::INIT_LAST_EXECUTION] qui fait appel à la variable parent LAST_EXECUTION du package Initialization. Cette variable (LAST_EXECUTION) est un string contenant la requête suivante :
 SELECT MAX(LastExecution) FROM LastExecution

Le résultat de la requête est stocké dans la variable de type Object GetLastExecution, ce type de variable ne peut être utilisé dans les expressions, il faudra donc penser à transférer la vlaeur dans une autre variable de type différent (UseLastExecution).

Execute SQL Task : SQL – Retrieve SOURCE_TAB CDC Infos

L’exécution de ce composant permet de vérifier si le CDC est activé au niveau de la table. Une reqûete SQL va interroger une table système pour récupérer un booléen.  La valeur est stocké dans la variable INIT_CDC_ENABLED que l’on renomme CDC_INFOS.

Composant Script

Les variables de type Object ne peuvent pas être utilisé directement dans les expressions.
C’est pour cela que l’on ré affecte la valeur dans une variable de type String afin d’utiliser le résultat dans les expressions.
De plus la variable GetLastExecution contient uniquement un seul enregistrement : la date du dernier chargement de données connue.
Pour transférer la valeur entre deux variable, il faut founir auc composant Script les variables que l’on souhaite manipuler.
Dans l’éditeur de code C# voici comment on affecte une valeur à une variable.
public void Main()
 {
 // TODO: Add your code here
Dts.Variables["UseLastExecution"].Value=Dts.Variables["GetLastExecution"].Value.ToString();
 Dts.TaskResult = (int)ScriptResults.Success;
 }

Flux de données: Manage Data Changes

Ce flux de données récupère les enregistrements liés aux changement de la table source. Chaque changements effectués sur la table source va créer une enregistrement dans une table système CDC associé à la table source.
Chaque enregistrement possède un numéro allant de 1 à 4 qui spécifie le type d’opérations effectuées (Insert/Update/Delete).
On traite alors les données selon leur type d’opération et on les charge dans la dimention du data warehoue de destination.

OLE DB Source  : Retrieve CDC Changes

La source de données OLE DB est une source dynamique… en effet lorsque l’on active le CDC sur une table source. Le CDC va créer une table système associé qui va contenir toutes les modifications.
Donc si l’on a une table « MyTable » appartenant au schéma dbo, lorsqu’on active le CDC on aura une table système « cdc.dbo_Mytable_CT » (CT pour capture Tracking)
La source OLE DB est dynamque grâce à sa configuration avancé :
Utiliser la bonne connexion à la table source.

La requête de ce composant est en fait un appel à la variable : User ::DynamicSQLStatement.
Cette variable est définis comme ceci :

L’expression utilisée est la suivante :
"SELECT __$start_lsn
 ,__$end_lsn
 ,__$seqval
 ,__$operation
 ,__$update_mask
 ,CustomerID
 ,CustomerName
 ,CustomerStatus,tran_end_time
FROM cdc.dbo_"+@[User::INIT_SOURCE_TAB]+"_CT INNER JOIN
cdc.lsn_time_mapping ON cdc.dbo_"+@[User::INIT_SOURCE_TAB]+"_CT.__$start_lsn = cdc.lsn_time_mapping.start_lsn
WHERE  lsn_time_mapping.tran_end_time > '" + @[User::UseLastExecution] +"'"

On utilise uen expression pricipalement afin d’avoir une clause FROM dynamique en fonction de table.
On batit le nom de table système CDC a partir du non de la source de donnée d’où l’ajout de  cdc.dbo_ en préfixe et _CT en suffixe.
La clause WHERE appelle la variable [User::UseLAstExecution] » pour récupérer uniquement les changments effectué sur la table source depuis le dernier chargement de données.

Par la suite il faut créer les champs d’entrées et de sorties :

Puis il faut les mapper :

Conditionnal Split : Split Insert Delete Update

La colonne __$operation de la source qui mappé à operation par la suite peut contenir 4 valeurs.
1 : signifie que que l’enregistrement dans la table CDC concerne une requête de supression (DELETE).
2 :signifie que l’enregistrement dans la table CDC  concerne une insertion (INSERT)
3 et 4 : signifient que l’enregistrement dans la table CDC concerce une mise à jour (UPDATE). Les opération de type 3 contiennent la valeur avant le changement et ceux de type 4 contienent les nouvelles valeurs.

Gestion des insertions

Gestion des supressions

Dans ce cas, lorsqu’un ordre DELETE est effectué sur la table source on ne va pas supprimer ce même enregistrement dans le data warehouse, on va juste changer le status de l’enregistrement pour le marquer comme indisponible ou non valable. RecordStatus= « KO »

Gestion des mises à jour

On utilise un composant SCD.

Limites de la solution

Uniquement pour les sources et les destinations de type SQL Server.
Uniquement avec les versions 2008.