0

I am trying to output the name of all the user variables and their values and then store the values in a table. I managed to get till the point of parsing the variables and their values, but right now it gives me all the variables (system and user). Also, I cant seem to figure out how to assign the values to a table. Any help will be appreciated..Below is what I have come up with till now.

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
//using System;
////using Microsoft.SqlServer.Dts.Runtime;
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();
        // Load a sample package that contains a variable that sets the file name.
        Package pkg = app.LoadPackage(
          @"C:\PackagePath" +
          @"Template0719.dtsx",
          null);
        Variables pkgVars = pkg.Variables;
        foreach (Variable pkgVar in pkgVars)
        {

            MessageBox.Show(pkgVar.Name);
            MessageBox.Show(pkgVar.Value.ToString());
        }
        Console.Read();
    }
    }

    }
rvphx
  • 2,324
  • 6
  • 40
  • 69

1 Answers1

1

This works for me

I created a package with a variable of each type, named after the SSIS data type thus, variable per data type

Using your app and pkg declaration/instantiations, I used the following

        boolean interactiveMode = true;
        foreach (Variable item in pkg.Variables)
        {
            try
            {
                if (!item.Namespace.Equals("System"))
                {
                    // item.Value.ToString()
                    string value = (item.Value == null) ? string.Empty : item.Value.ToString();
                    string name = item.Name;
                    string scope = item.Namespace;
                    string type = item.DataType.ToString();
                    string output = string.Format("Name {0, -20} | Scope {1, -10} | Data Type {2, -10} | Value {3, -20}", name, scope, type, value);
                    if (interactiveMode)
                    {
                        MessageBox.Show(output);
                    }
                    else
                    {
                        bool fireAgain = true;
                        Dts.Events.FireInformation(0, "Variable enumeration", output, string.Empty, 0, ref fireAgain);
                    }

                }

            }
            catch (Exception ex)
            {

                Dts.Events.FireError(0, "enumerate", ex.ToString(), string.Empty, 0);
            }
        }

That generates expected values

---------------------------

---------------------------
Name VariableDbNull       | Scope User       | Data Type Empty      | Value                     
---------------------------
OK   
---------------------------

---------------------------

---------------------------
Name VariableDouble       | Scope User       | Data Type Double     | Value 0                   
---------------------------
OK   
---------------------------
billinkc
  • 59,250
  • 9
  • 102
  • 159
  • Thank you for the response. How can I extend this script to dump data to a destination table? – rvphx Jul 27 '12 at 19:57
  • I posted a new question of what I am trying achieve. Please take a look and let me know if you need any other information or if there is any other way of doing what I am trying to do. Link to the question: http://stackoverflow.com/questions/11695821/dump-ssis-user-variable-name-and-value-in-sql-server-table – rvphx Jul 27 '12 at 21:32