[SSIS] Data Transformation/Caching : utilisation du Cache Connection Manager (CCM), ou comment réutiliser le cache ?

Ce billet présente le Cache Connection Manager (CCM) et un modus operandi de sa mise-en-œuvre pour la réutilisation de données chargées en mémoire.

Vous pouvez jeter un coup d’œil ici, pour toute information complémentaire sur les modes de caching avec SSIS. Et là : http://msdn.microsoft.com/en-us/library/bb895289.aspx

Avant de commencer…

Présentation des besoins et intérêt de l’utilisation du Cache Connection Manager (CCM)

Concernant les prérequis relatifs à notre travail, nous reprendrons le billet relatif au chargement incrémental d’UPSERTs via connexion OLE DB, dont voici les data flows (contenus dans un control flow appelé Data Flow Task – UPSERT), pour rappel :


Comme déjà indiqué dans ledit billet, le but des différents flux de contrôle est de permettre au package SSIS d’effectuer une synchronisation des opérations d’insertion et de mise-à-jour entre un fichier Excel contenant des informations sur des employés (identifiant, nom, prénom et âge) et une table Employes stockée au sein d’une base de données SQL Server MaBase. Les opérations sont faites avec notamment l’utilisation d’une recherche de transformation (lookup transformation) exploitant une connexion OLE DB.

Par rapport au caching, le mode Full cache a été choisi de sorte que les données à traiter sont mises en cache avant chaque lookup. Cependant, la mise-en-cache n’étant pas persistante, le cache du lookup ne sera libéré qu’à la fin de l’exécution du package, d’où l’intérêt de notre nouveau besoin : permettre la persistance des données en cache afin de garantir leur réutilisabilité grâce au CCM.

Rappelons que la structure de notre table Employes est la suivante :


Conseils et bonnes pratiques d’utilisation du CCM

Vous pouvez utiliser le CCM si :

  • Vous souhaitez réduire les charges sur la base de données cible.
  • Vous cherchez à mutualiser l’utilisation du cache par différents lookups.

Notez cependant que :

  • Certains types de données ne peuvent pas être mis en cache, comme les LOB (données de type Large Object Binary), le type TEXT.
  • Le cache stocke les données en clair. Il est donc recommandé de ne pas stocker des données sensibles au sein du cache.
  • L’utilisation du CCM ne signifie pas forcément des temps de réponse plus rapides que dans le cas d’une connexion OLE DB. En effet, il peut arriver que les accès disques puissent être plus rapides que la phase de pré-création du cache. Dans tous les cas, un mot d’ordre : testez.
A noter également que l’utilisation du CCM est comparable à l’utlisation d’une connexion OLE DB en Full cache mode, à la différence que dans le premier cas, il y a réutilisation du cache, en cas de rééxecution du package SSIS.

Modus operandi

Les phases de nommage sont facultatives.

    Implémentation du mécanisme de chargement de données en cache

  • Au sein de l’onglet Control Flow, faites glisser un composant Data Flow Task, et renommez-le Data Flow Task – Load data on cache :


  • Placez une contrainte de précédence positive allant de Data Flow Task – Load data on cache vers le control flow
    Data Flow Task – UPSERT relatif aux UPSERTs :

  • Sur la zone de design Data Flow de Data Flow Task – Load data on cache  (accessible via double-clic sur le composant, par exemple) :
    • Faites glisser un composant OLE DB Source, ainsi qu’un autre appelé Data Transform, puis reliez-les par une contrainte de précédence positive partant d’OLE DB Source – Employés :
    • Configurez OLE DB Source – Employés de façon à ce que la table dbo.Employés soit utilisée (vous pouvez, ici, filtrer les enregistrements à mettre en cache via une clause WHERE, d’ailleurs, ou limiter leur nombre via le mot-clé TOP) :


    • Dans les propriétés de Cache Transform :
      • Dans l’onglet Connection Manager,
        cliquez sur New :


      • Spécifiez (si souhaité) le nom du CCM et le chemin d’accès et nom du fichier de caching à utiliser :


Si le fichier spécifié n’existe pas, il sera automatiquement créé lors du lancement du composant Data Transform :

  • Dans l’onglet Columns, les colonnes du fichier Excel sont automatiquement ajoutées (via l’Excel Source). Il reste à choisir une colonne d’index (ID, dans notre cas) qui sera utilisée pour la jointure (en jouant avec les valeurs d’Index Position, où 1 est l’index, 0 les autres colonnes) :


Pour des raisons de lisibilité, vous pouvez éventuellement préfixer chaque nom de colonne à cacher d’un « Cached_ », comme ci-dessous :


  • Après validation de la création du CCM ci-dessus, effectuez les correspondances entre les colonnes de la source de données et celles qui seront en cache :


Voici ce que donne le contenu du control flow Data Flow Task – Load date on cache :


    Activation de la réutilisabilité du cache par les data flows

Revenons maintenant à nos tâches relatives à la synchronisation des insertions et mises-à-jour de données, à savoir : Data Flow Task – UPSERT.

Pour forcer la réutilisation du cache, il faut effectuer quelques ajustements au niveau du composant Lookup :


Pour ce faire, dans les propriétés de chaque composant Lookup concerné :

  • Choisissez Cache connection manager comme type de connexion :


  • Validez l’équijointure (dans la section Columns), en utilisant la colonne ID du fichier Excel convertie :


Validez les modifications en cliquant sur OK.

Pour aller plus loin…

Vous pouvez garder un œil ici, pour consulter les autres billets relatifs au SSIS caching. Ou , pour consulter les autres billets relatifs à la transformation de données.


Publicités

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s