Aujourd’hui, un petit article pour expliquer comment travailler avec une procédure stockée en entrée d’un flux SSIS.

L’idée ici est de présenter 3 façons différentes de récupérer les données renvoyées par une procédure stockée.

  1. La première se base sur l’utilisation de cette source dans un DataFlow
  2. La deuxième sur un le ControlFlow avec itération
  3. La troisième sur l’utilisation d’un DataTable en mémoire qu’on va pouvoir réutiliser partout Sourire

 

DataFlow

 

Première solution, la plus simple à mon sens. Traiter la procédure stockée en tant que source de données dans un DataFlow :

image

Dans le DataFlow, j’utilise un composant source avec connexion Ole DB.

image

Attention à la configuration de votre source de données. Le passage de paramètres (pour Ole DB) se passe comme suit :

  1. Le paramètre dans l’appel de la procédure stockée se fait à l’aide du point d’interrogation
  2. Le mapping des paramètres s’effectue avec le nom du paramètre attendu par la procédure stockée.

image

image

Voilà, première méthode simple et efficace Sourire

 

ControlFlow, Execute Task SQL, Foreach Enumerator

 

L’idée ici est de se placer au niveau du Control Flow et de passer par une tache Execute Task SQL.

Attention, le principe est le même que l’Ole DB source du DataFlow excepté le mapping des paramètres !

  1. Le paramètre dans l’appel de la procédure stockée se fait à l’aide du point d’interrogation
  2. Le mapping des paramètres s’effectue avec l’indice (de base 0) du paramètre !

image

image

Tiens d’ailleurs, pour informations, voici le lien qui spécifie Comment paramétrer cette tache SUIVANT le type de CONNEXION (Ole DB, ADO, ADO.NET, ODBC) :

http://msdn.microsoft.com/en-us/library/cc280502.aspx

image

(Oui je sais, c’est très très moche, mais c’est comme ça ! Sourire)

A partir de là, on stock le résultat dans une variable Object dans l’onglet ResultSet.

Pour information, l’objet stocké est en réalité un ADODBRecordset. Ceci nous servira dans une prochaine méthode.

De là, on peut facilement itérer sur chaque ligne, via la container Foreach :

image

Le container Foreach est configuré via le Foreach ADO Enumerator :

image

Vous pouvez de là mapper chaque colonne dans une variable temporaire et traiter chaque ligne comme bon vous semble Sourire

 

Control Flow, Execute Task SQL, DataTable

 

A partir de la solution précédente, au lieu d’itérer sur chaque ligne, on peut tout simple récupérer le ADODBRecordset de notre variable objet et remplir une DataTable, déconnectée, en mémoire.

Pour se faire, il suffit de créer un script qui va remplir la DataTable :

image

Pour le contenu et paramétrage du Scipt SQL Task, voir la méthode précédente

Voici le contenu du script component qui lui est chargé de remplir la DataTable:

/// <summary>
/// This method is called when this script task executes in the control flow.
/// Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
/// To open Help, press F1.
/// </summary>
public void Main()
{
    // TODO: Add your code here
     DataTable dtRows = newDataTable();

OleDbDataAdapter adapt = new OleDbDataAdapter();
adapt.Fill(dtRows, Dts.Variables["User::StoredResults"].Value);

Dts.TaskResult = (int)ScriptResults.Success;
}

 

Simple !

Cet exemple, un peu plus complexe me permet pourtant de simplifier et d’améliorer les performances globales de mon package, dans un scénario précis : La réutilisation du jeu de résultat dans le package global.

A supposer que le résultat de cette procédure stockée soit utilisée à plusieurs endroits, j’ai rempli une Table en mémoire que je peux réutiliser partout dans mon package sans pour autant ré exécuter ma procédure stockée.

D’ailleurs voici la méthode pour utiliser votre source DataTable en mémoire dans un DataFlow :

Tout d’abord, utilisez un script component en tant que source :

image

Ensuite configurez à la main; les colonnes de sorties :

image

Et enfin dans le script, générez le flux de sortie. N’oubliez pas de bien passer votre variable globale contenant la DataTable Sourire

image

Voici le script de construction des lignes:

public override void CreateNewOutputRows()
 {
     DataTable dt = Variables.ProductDataTable as DataTable;
     foreach (DataRow dr in dt.Rows)
     {
Output0Buffer.AddRow();

if (dr["BOMLevel"] == System.DBNull.Value)
Output0Buffer.BOMLevel_IsNull = true;
else
Output0Buffer.BOMLevel = (short)dr["BOMLevel"];

if (dr["ComponentDesc"] == System.DBNull.Value)
Output0Buffer.ComponentDesc_IsNull = true;
else
Output0Buffer.ComponentDesc = (String)dr["ComponentDesc"];

if (dr["ComponentID"] == System.DBNull.Value)
Output0Buffer.ComponentID_IsNull = true;
else
Output0Buffer.ComponentID = (int)dr["ComponentID"];

if (dr["ListPrice"] == System.DBNull.Value)
Output0Buffer.ListPrice_IsNull = true;
else
Output0Buffer.ListPrice = (decimal)dr["ListPrice"];

if (dr["ProductAssemblyID"] == System.DBNull.Value)
Output0Buffer.ProductAssemblyID_IsNull = true;
else
Output0Buffer.ProductAssemblyID = (int)dr["ProductAssemblyID"];

if (dr["RecursionLevel"] == System.DBNull.Value)
Output0Buffer.RecursionLevel_IsNull = true;
else
Output0Buffer.RecursionLevel = (int)dr["RecursionLevel"];

if (dr["StandardCost"] == System.DBNull.Value)
Output0Buffer.StandardCost_IsNull = true;
else
Output0Buffer.StandardCost = (decimal)dr["StandardCost"];

if (dr["TotalQuantity"] == System.DBNull.Value)
Output0Buffer.TotalQuantity_IsNull = true;
else
Output0Buffer.TotalQuantity = (decimal)dr["TotalQuantity"];

}

 

Note pour les puristes du code, qui vont objecter qu’une forme concaténée du if aurait été plus élégante et judicieuse (ou qui trouve que vérifier une valeur True pour ensuite affecter à True une autre valeur est une hérésie Clignement d'œil)

Il est impossible d’affecter False à la propriété VAR_IsNull. Exemple sur la propriété TotalQuantity :

public bool TotalQuantity_IsNull
 {
     set
     {
         if (value)
         {
             SetNull(3);
         }
         else
         {
             throw new InvalidOperationException("IsNull property cannot be set to False. Assign a value to the column instead.");
         }
     }
 }

 

Voilà j’y suis pour rien, ne taper pas le messager Sourire

Bref, l’exécution du composant fonctionne et vous pouvez par la suite utiliser votre flux comme bon vous semble !

image

(Presque) Simple !

Les sources du projet contiennent les 3 méthodes dans un package.

Ces sources sont utilisées sur SQL SERVER SSIS 2012 et la base de données AdventureWorks.

SSIS StoredProcedure.zip

Bon traitement SSIS !!