SSIS Tips : les procédures stockées utilisant des tables temporaires

J’ai été récemment confronté à un problème avec l’utilisation de SSIS et je souhaite vous faire partager l’astuce qui m’a permis de m’en sortir 😉

Le projet sur lequel je travaille utilise de nombreux traitements SSIS pour importer de la donnée et la manipuler (traitements, calculs…). Ces traitements utilisent le plus souvent un Data Flow composé d’une source OleDB et d’une procédure stockée. Le problème est le suivant : certaines de mes procédures stockées utilisent des tables temporaires et dans ce cas, SSIS n’est pas capable de générer le mapping entre les colonnes sources et les colonnes cibles !

Voici une table que j’ai utilisée pour mon scénario de test ainsi qu’une procédure stockée permettant de reproduire le scénario posant problème :

Et voilà l’erreur obtenue dans SSIS :

J’ai trouvé 4 solutions, plus ou moins élégantes et performantes pour résoudre ce problème.

Solution 1 : SET FMTONLY OFF

Attention, cette astuce ne fonctionne qu’avec SSIS 2008 !

Pour permettre à SSIS de récupérer les métadonnées de la procédure stockée, je vais lui rajouter au tout début la directive « SET FMTONLY OFF« . Par défaut, SSIS utilise la valeur ON pour cette directive et dans ce cas, les tables temporaires ne sont pas créées. C’est pour cela que les métadonnées ne peuvent pas être déterminées.

Le principal inconvénient de cette méthode est que les performances peuvent être considérablement dégradée. En effet, pour déterminer les métadonnées, SSIS va exécuter la procédure plusieurs fois ! On peut configurer SSIS pour réduire ce nombre d’exécution en modifiant les propriétés suivantes :

  • Sur le DataFlow, DelayValidation à TRUE
  • Sur les connections source et destination, ValidateExternalMetadata à FALSE

Solution 2 : Lister explicitement les métadonnées

Attention, cette astuce ne fonctionne qu’avec SSIS 2008 !

Au début de la procédure stockée, je vais ajouter une clause SELECT listant l’ensemble des colonnes et leurs types retournés par la procédure. C’est une sorte de contrat. Cette clause SELECT doit être précédée d’une instruction « IF 1 = 0« . SSIS sera donc en mesure de déterminer les métadonnées.

L’inconvénient de cette méthode est que c’est un peu lourd à mettre en place et qu’il faut penser à mettre à jour ce SELECT si le format de retour de la procédure change. Par contre, cela évite de modifier le type des tables et il n’y a aucun impact en termes de performances !

Solution 3: Utilisation des variables de type table

Cette astuce fonctionne avec SSIS 2008 et supérieur !

Pour éviter les problèmes liés aux tables temporaires, il suffit de les remplacer par des variables de type table ! En effet, ces dernières ne posent aucun problème à SSIS pour lire les métadonnées. Attention tout de même aux problématiques de performance dans certains cas…

Solution 4 : WITH RESULT SETS

Attention, cette astuce ne fonctionne qu’avec SSIS 2012 et supérieur !

Directement à partir de l’assistant d’une source OleDB, il faut ajouter l’instruction « WITH RESULT SETS » avec l’ensemble des colonnes et leur type représentant le format de retour de la procédure.

Cette instruction permet d’autoriser SSIS à bypasser l’étape « sp_describe_first_result_set » et à lui indiquer d’utiliser les métadonnées fournies via le « WITH RESULT SETS« . On retrouve dans cette solution le même inconvénient que celui de la solution 2 : il est nécessaire de mettre à jour ce code en cas d’évolution du format de retour.

DataFlow - WithResultSets

Nous avons fait le tour des différentes solutions pour pallier à ce problème de table temporaire en SSIS. En fonction de la version cible (2008 ou 2012 et +), je vous conseille fortement les solutions 2 et 4. La solution 3 peut-être envisagée pour simplifier la mise en place, mais il faut bien garder à l’esprit les contraintes de performance.

J’espère que cette astuce pourra vous aider !

Matthieu Anceret

Ingénieur en informatique chez C2S (groupe Bouygues), je suis passionné par tout ce qui touche à la technologie et la mobilité, et plus particulièrement à l'univers Android. Féru de domotique et de ses aspects multimédia, la moindre occasion est bonne pour tester et expérimenter les dernières nouveautés dans ce domaine. À travers ce blog, j'espère vous transmettre ma passion et mes connaissances. Bonne lecture !

Vous aimerez aussi...

Laisser un commentaire

%d blogueurs aiment cette page :