Les Journées SQL Server – Webcast en ligne

Bonne année à tous ! Certes un peu en retard mais j’étais en vacance et maintenant c’est la reprise 🙂

Premier article 2012 basé sur un évènement de fin 2011 : Les Journées SQL Server.

Il y a un site dédié aux vidéos de l’évènement avec les KeyNotes par thème BI / SQL Server SQL Azure / Appliances HP-Microsoft: http://www.microsoft.com/france/serveur/sql/journees-sql-server.aspx

Vous trouverez la liste des webcasts sur le blog de  Jean Pierre Riehl : http://blog.djeepy1.net/2012/01/06/tous-les-webcasts-des-journees-sql-server/

Le site des showcases Microsoft qui regroupe les vidéos de l’évènement et bien plus encore ! : http://www.microsoft.com/fr-fr/showcase/Search.aspx?phrase=Les+journ%c3%a9es+SQL+Server

Et pour vraiment finir vous pouvez me retrouver dans les sessions suivantes :

NOTE : une mauvaise vidéo à été uploadé à la place…cela va être corrigé d’ici peu.

C’est la première fois que je participe à un évènement de la sorte en tant que speaker et j’en garde un bon souvenir.

En me reécoutant j’ai noté pas mal de trucs à corriger pour la prochaine fois je ferais attention… N’hésitez pas à me faire des retours sur ces sessions Bonnes ou Mauvaises car je veux  m’ améliorer !

Merci aux Sponsors  ! J’ai oublié de les remercier dans la sessions DQS, sans eux cet évènement n’aurait pas pu se faire.

Publicités

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.