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 :

CREATE TABLE [dbo].[TableTest](
	[Id]            [uniqueidentifier]  NOT NULL,
	[CreatedOn]     [datetimeoffset](7) NOT NULL,
	[ModifiedOn]    [datetimeoffset](7) NOT NULL,
	[Name]          [varchar](100)      NOT NULL,
	[Type]          [int]               NOT NULL,
	[Description]   [varchar](1000)     NULL
);

ALTER PROCEDURE [dbo].[Test]	
AS
BEGIN
	CREATE TABLE #TestTempTable 
	(
		Id			UNIQUEIDENTIFIER	NOT NULL,
		CreatedOn	DATETIMEOFFSET(7)	NOT NULL,
		Name		VARCHAR(100)		NOT NULL,
		Description VARCHAR(1000)		NULL
	);

	INSERT INTO #TestTempTable
	SELECT Id, CreatedOn, Name, Description
	FROM TableTest;

	SELECT Id, CreatedOn, Name, Description
	FROM #TestTempTable;
END

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 !

ALTER PROCEDURE [dbo].[Test]	
AS
BEGIN
	IF 1 = 0
	BEGIN
		SELECT
			  CAST(NULL AS UNIQUEIDENTIFIER) AS Id
			, CAST(NULL AS DATETIMEOFFSET(7)) AS CreatedOn
			, CAST(NULL AS VARCHAR(100)) AS Name
			, CAST(NULL AS VARCHAR(1000)) AS Description
	END;
	
	CREATE TABLE #TestTempTable 
	(
		Id			UNIQUEIDENTIFIER	NOT NULL,
		CreatedOn	DATETIMEOFFSET(7)	NOT NULL,
		Name		VARCHAR(100)		NOT NULL,
		Description VARCHAR(1000)		NULL
	);

	INSERT INTO #TestTempTable
	SELECT Id, CreatedOn, Name, Description
	FROM TableTest;

	SELECT Id, CreatedOn, Name, Description
	FROM #TestTempTable;
END

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…

ALTER PROCEDURE [dbo].[Test]	
AS
BEGIN
	DECLARE @TestTempTable TABLE
	(
		Id		UNIQUEIDENTIFIER	NOT NULL,
		CreatedOn	DATETIMEOFFSET(7)	NOT NULL,
		Name		VARCHAR(100)		NOT NULL,
		Description     VARCHAR(1000)		NULL
	);

	INSERT INTO @TestTempTable
	SELECT Id, CreatedOn, Name, Description
	FROM TableTest;

	SELECT Id, CreatedOn, Name, Description
	FROM @TestTempTable;
END

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.

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 !