SSIS Tips : utilisation et manipulation des VARCHAR(MAX)

J’ai récemment eu besoin de manipuler des données SQL du type VARCHAR(MAX) via SSIS. Dans SQL Server, ce type permet de stocker des données de type chaîne de caractères d’une taille jusqu’à 2 Go. Voilà le scénario que je souhaitais réaliser :

  • Une procédure stockée me retourne une donnée de type VARCHAR(MAX) représentant un contenu XML
  • J’appelle cette procédure dans une tâche de type « Script Task » et je stock le résultat dans une variable
  • J’utilise cette variable en tant que paramètre entrant dans une OleDB Source d’un DataFlow
  • Je récupère le résultat via une OleDB Destination

A 1ère vue, ce scénario ne pose pas de soucis particulier, mais nous allons voir que SSIS a une gestion très partielle du type de données VARCHAR(MAX).

L’objectif de ma 1ère procédure est de me retourner un XML regroupant un ensemble de villes par département (à partir du table stockant la commune et son département) :

Attention, SQL Server Management Studio et Visual Studio tronque à l’affichage les résultats (au alentour de 43680 caractères). Gare donc au copier/coller !

Voilà le résultat partiel :

Côté SSIS, au niveau de la Script Task, il est nécessaire de faire attention au type de résultat qui doit être « Full Result Set« . La cible est une variable SSIS de type « Object« . Ces 2 points sont importants car cela va éviter de dénaturer le résultat et nous permettre de manipuler notre VARCHAR(MAX) sans qu’il soit tronqué.

Je souhaite maintenant passer ma variable en paramètre de ma 2nd procédure stockée dans mon DataFlow (OleDB Source). Cette procédure prend donc en paramètre une variable de type VARCHAR(MAX).

  • 1er problème, étant donné que l’on a choisi un résultat de type « Full Result Set« , le contenu de notre variable n’est pas quelque chose de connu par SQL Server. L’astuce consiste à mettre en place une structure « Foreach » fictive pour parcourir notre variable et accéder à la véritable donnée (notre VARCHAR(MAX)). Cette boucle ne fera donc qu’un seul tour car je n’ai qu’une seule ligne de résultat. Il faut donc créer une nouvelle variable de type « Object » pour stocker la valeur à chaque tour de boucle. On va ensuite choisir un énumérateur du type « Foreach ADO Enumerator » et spécifier la variable de destination ainsi que la source de la boucle.

  • 2nd problème, notre variable est mal reconnue lors de l’appel à la procédure stockée dans l’OleDB Source. Il faut donc forcer le cast de notre variable via l’instruction suivante : VARCHAR(MAX) : DECLARE @casted AS VARCHAR(MAX) = ?. On peut ensuite appeler notre procédure stockée de manière standard : EXECUTE MySP @xml = @casted .

J’espère en tout cas 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

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.

%d blogueurs aiment cette page :