Cet article a pour but de vous préconiser une solution de contournement dans le cadre de vos développements avec SSIS où vous faites face à de contraintes de performances altérées par l’utilisation massive de lookups en full cache gourmands en ressource mémoire. Ainsi si vous êtes dans un des cas de figures suivants:
- Package SSIS avec une multitude de lookups full cache en cascade
- Un nombre d’enregistrement dans ces lookups dépassant le million de ligne
- Plantage à l’exécution du package avec l’erreur suivante « A buffer failed while allocating 10485088 bytes »
Initialement, lorsque vous avez affaire à ce genre de contraintes, la modification des propriétés suivantes du Data Flow Task,
- Default Buffer Size
- DefaultBufferMaxRows
peut être une des pistes de résolution du problème.
Cependant, il est préférable d’imaginer une architecture différente pour votre package afin d’améliorer la performance.
En résumé, l’utilisation à outrance de lookups en full cache avec une connexion de type OLE DB connections manager (choix par défaut) est à proscrire pour les raisons précitées :
La solution palliative à ce problème est l’utilisation des lookups avec une connexion de type Cache connection manager :
Ce type de connexion induit l’utilisation en amont d’un nouveau composant appelé le Cache Transform:
Avant de vous initier à l’utilisation du composant Cache Transform, il est à noter que les colonnes au format Binary Large Object (BLOB) data types DT_TEXT, DT_NTEXT, and DT_IMAGE ne sont pas supportées, ainsi si votre requête en possède, pensez bien à les convertir en un type supporté par le composant.
Ce composant peut se définir comme un outil de stockage en mémoire tampon. Il s’avère utile pour dé-sérialiser l’enchaînement des composants lookups et permettre ainsi le chargement des données via une phase de pré chargement parallélisée (Step 1 dans l’exemple suivant) :
Double cliquer sur le data flow qui va vous permettre de charger les tables de référence.
- Faire un Drag and Drop du composant Cache Transform depuis la boite à outil SSIS
- Raccorder le à votre requête source (Type OLE DB Source)
- Double cliquer sur le composant Cache Transform
- Ajouter une nouveau gestionnaire de connexions (Connexion Managers) qui apparaîtra dans l’onglet éponyme ci-dessous (A noter qu’il faut autant de connexion qu’il y a de lookups à effectuer
- Cliquer sur le bouton New afin de lancer l’assistant de création de la nouvelle connexion:
- Dans l’onglet Général, donner un nom à votre connexion :
Ensuite cliquer sur l’onglet Columns , vous obtenez dans la fenêtre suivante la liste des colonnes de référence disponibles :
Le point important à cet instant est de repérer l’ensemble des champs nécessaires à la jointure avec la table source.
Par exemple si vous avez , trois clefs de jointure nommées : « ID1 » , « ID2″, » ID3″ et « ID4 », la propriété Index Position devra être incrémentée de 1 à 4 pour les quatre colonnes concernées. Les autres éléments restant à zéro.
Une fois les modifications validées, aller dans le composant lookup et procéder comme suit :
- Choisir le mode Cache Connection Manager dans l’onglet Général
- Ensuite, dans l’onglet Connection, Sélectionner le gestionnaire de connexion précédemment créé :
- Assurer vous que les clefs de jointures soient correctes dans l’onglet Columns
Et pour finir, valider la modification du Lookup.
L’implémentation du composant Cache Transform est maintenant terminée, vous pouvez lancer votre package sans encombre.
En espérant que cet article vous apporte un peu plus de clarté et de compréhension sur le sujet.
Thank you for reading, Feel free to answer and Comment if you have any questions on this topic.
