Dump SSIS USER nom de variable et la valeur dans la table de SQL Server

Question

Objectif : Récupérez toutes les variables utilisateur qui se trouvent dans le paquet SSIS et écrivez le nom des variables et leurs valeurs dans une table SQL Server 2008.

Ce que j'ai essayé : J'ai obtenu un petit "script". Tâche " pour afficher le nom des variables et leur valeur. Le script est le suivant.

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
namespace ST_81ec2398155247148a7dad513f3be99d.csproj
{
    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {

        #region VSTA generated code
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

    public void Main()
    {

        Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();
        Package pkg = app.LoadPackage(
          @"C:\package.dtsx",
          null);
        Variables pkgVars = pkg.Variables;

        foreach (Variable pkgVar in pkgVars)
        {
            if (pkgVar.Namespace.ToString() == "User")
            {
                MessageBox.Show(pkgVar.Name);
                MessageBox.Show(pkgVar.Value.ToString());
            }
        }
        Console.Read();
    }
    }

    }

Ce qui doit être fait : J'ai besoin de prendre cela et de transférer les valeurs dans une table de base de données. J'essaie de travailler sur un composant script pour cela, mais en raison de mon manque de connaissances en matière de scripting .net, je n'ai pas réussi à m'approcher de la ligne d'arrivée. Voici ce que j'ai fait du côté des composants.

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using Microsoft.SqlServer.Dts;
using System.Windows.Forms;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{

   public override void CreateNewOutputRows()
    {

        #region Class Variables
        IDTSVariables100 variables;
        #endregion

        variables = null;

        this.VariableDispenser.GetVariables(out variables);
        foreach(Variable myVar in variables)
        {
            MessageBox.Show(myVar.Value.ToString());
            if (myVar.Namespace == "User")
            {
                Output0Buffer.ColumnName = myVar.Value.ToString();
            }
        }
    }

}

Answer 1

Je peux penser à deux façons de résoudre ce problème pour le moment.

1. Utilisez la même tâche script pour insérer les valeurs dans la base de données.
2. utilisation de la boucle Foreach pour énumérer les variables du paquet ssis (comme vous l'avez demandé)

Le tableau script pour insérer le nom et la valeur de la variable est

CREATE TABLE [dbo].[SSISVariables]
(
[Name] [varchar](50) NULL,
[Value] [varchar](50) NULL
)

1. Utiliser Script task et écrire le code suivant

[System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
    private static string m_connectionString = @"Data Source=Server Name;
    Initial Catalog=Practice;Integrated Security=True";
   public void Main()
    {
       List<SSISVariable> _coll = new List<SSISVariable>();
        Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();
        Package pkg = app.LoadPackage(PackageLocation,Null);
                   Variables pkgVars = pkg.Variables;

        foreach (Variable pkgVar in pkgVars)
        {
            if (pkgVar.Namespace.ToString() == "User")
            {
                _coll.Add(new SSISVariable ()
                {
                 Name =pkgVar.Name.ToString(),
                 Val =pkgVar .Value.ToString () 
                });
           }
        }
        InsertIntoTable(_coll);
        Dts.TaskResult = (int)ScriptResults.Success;
    }

    public void InsertIntoTable(List<SSISVariable> _collDetails)
    {  
       using (SqlConnection conn = new SqlConnection(m_connectionString))
        {
            conn.Open();
            foreach (var item in _collDetails )
            {
             SqlCommand command = new SqlCommand("Insert into SSISVariables values (@name,@value)", conn);
             command.Parameters.Add("@name", SqlDbType.VarChar).Value = item.Name ;

             command.Parameters.Add("@value", SqlDbType.VarChar).Value = item.Val ;
             command.ExecuteNonQuery();    
            }
        }
     }
  }

   public class SSISVariable
   {
    public string Name { get; set; }
    public string Val { get; set; }
   }

Explication:Dans cet article, je crée une classe qui a des propriétés Name et Val. Récupérez les variables du paquet et leur valeur en utilisant votre code et stockez-les dans une collection. List<SSISVariable> Puis on passe cette collection à une méthode ( InsertIntoTable ) qui insère simplement la valeur dans la base de données en énumérant la collection .

Note :

There is a performance issue with the above code ,as for every variable 
im hitting the database and inserting the value.You can use
[TVP][1]( SQL Server 2008)   or stored procedure which takes
 xml( sql server 2005) as input. 

2. utiliser la boucle ForEach

Design

Étape 1 : créer une variable VariableCollection qui est de type System.Object et un autre variable Item qui est de type String pour stocker le résultat de la boucle Foreach

Etape 2 : Pour la 1ère tâche script .

VariableCollection est utilisé pour stocker le nom de la variable et sa valeur

Étape 3 : à l'intérieur de la tâche script. Main Method écrivez le code suivant

  public void Main()
   {
     ArrayList _coll = new ArrayList(); 

        Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();
        Package pkg = app.LoadPackage(Your Package Location,null);

        Variables pkgVars = pkg.Variables;
        foreach (Variable pkgVar in pkgVars)
        {
            if (pkgVar.Namespace.ToString() == "User")
            {
                _coll.Add(string.Concat ( pkgVar.Name,',',pkgVar.Value ));
            }
        }
        Dts.Variables["User::VariableCollection"].Value = _coll;
        // TODO: Add your code here
        Dts.TaskResult = (int)ScriptResults.Success;
    }

Etape 4 : Faites glisser une boucle Foreach et dans l'expression utilisez Foreach from Variable Enumerator

Étape 5 : La boucle Foreach énumère les valeurs et les stocke dans une variable. User::Item

Etape 6 : A l'intérieur de la boucle foreach, faites glisser une tâche script et sélectionnez la variable

readonly variables   User::Item 

étape 7 : Ecrivez le code ci-dessous dans la méthode principale

   public void Main()
    {
      string name = string.Empty;
      string[] variableCollection;
      variableCollection = Dts.Variables["User::Item"].Value.ToString().Split(',');
      using (SqlConnection conn = new SqlConnection(m_connectionString))
        {
            conn.Open();
            SqlCommand command = new SqlCommand("Insert into SSISVariables values (@name,@value)", conn);
            command.Parameters.Add("@name", SqlDbType.VarChar).Value = variableCollection[0];

            command.Parameters.Add("@value", SqlDbType.VarChar).Value = variableCollection[1];

            command.ExecuteNonQuery();
        }
        // TODO: Add your code here
        Dts.TaskResult = (int)ScriptResults.Success;
    }

Explication : Dans la boucle Foreach, je ne peux énumérer qu'une seule variable. La logique est donc de passer le nom de la variable et sa valeur dans une seule variable. Pour cela, j'ai concaténé le nom et la valeur.

 string.Concat ( pkgVar.Name,',',pkgVar.Value )

script la tâche dans la boucle foreach divise simplement la variable et la stocke dans un tableau de chaîne et ensuite u peut accéder au nom et à la valeur en utilisant l'index du tableau et le stocker dans la base de données.