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) :

SELECT
	[Departements] = CAST
	(
		(
			SELECT
				[code] = dep.Departement,
				(
					SELECT [nom] = ville.Commune
					FROM [Villes] ville
					WHERE ville.Departement = dep.Departement
					FOR XML AUTO, TYPE
				)
			FROM
				[Villes] dep
			GROUP BY
				dep.Departement
			ORDER BY 
				dep.Departement
			FOR XML AUTO, ROOT('root')
		) AS VARCHAR(MAX)
	);

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 :

<root>
	<dep code="1">
		<ville nom="L ABERGEMENT CLEMENCIAT"/>
		<ville nom="AMBLEON"/>
		...
	</dep>
	<dep code="2">
		<ville nom="AGUILCOURT"/>
		<ville nom="AISONVILLE ET BERNOVILLE"/>
		...
	</dep>
	...
</root>

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 !