title: Dump SSIS USER variable name and value in SQL Server table
body: <p><strong>Purpose:</strong> Fetch all the user variables that are in the SSIS package and write the variable name and their values in a SQL Server 2008 table. </p>
  
  <p><strong>What have I tried:</strong> I got a small "Script <strong>Task</strong>" working to Display the variable name and their value. The script is as follows.</p>
  
  <pre><code>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();
      }
      }
  
      }
  </code></pre>
  
  <p><strong>What needs to be done:</strong> I need to take this and dump the values to a database table. I am trying to work on a <strong>script component</strong> for this, but due to lack of knowledge of .net scripting, I havent gotten anywhere close to the finish line. This is what I have done on the component side. </p>
  
  <pre><code>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();
              }
          }
      }
  
  }
  </code></pre>